Python For Data Analysis Workbook III.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python for Data Analysis - Workbook 3 (Cleaning, Prepping, and Wrangling)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preliminaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dropping null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2     True\n",
       "3    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas defaults to showing numeric values as NaN. The Python 'None' value is also treated as NA (not available)\n",
    "# Can test for null values with the isnull() method\n",
    "\n",
    "string_data = pd.Series(['aardvark', 'artichoke', None, 'avocado'])\n",
    "string_data.isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1.0\n",
      "1    NaN\n",
      "2    3.5\n",
      "3    7.0\n",
      "dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    1.0\n",
       "2    3.5\n",
       "3    7.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Very easy to drop missing data in a Series using the dropna() method\n",
    "\n",
    "data = pd.Series([1, np.nan, 3.5, 7])\n",
    "print(data)\n",
    "data.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     0    1    2\n",
      "0  1.0  6.5  3.0\n",
      "1  1.0  NaN  NaN\n",
      "2  NaN  NaN  NaN\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2\n",
       "0  1.0  6.5  3.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For DataFrames it is more complex, because Pandas automatically drops an entire row if it contains an NA value\n",
    "\n",
    "df = pd.DataFrame([[1., 6.5, 3.], [1., np.nan, np.nan], [np.nan, np.nan, np.nan]])\n",
    "print(df)\n",
    "df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2\n",
       "0  1.0  6.5  3.0\n",
       "1  1.0  NaN  NaN"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Passing in the how ='all' flag will only drop rows that are all NA\n",
    "\n",
    "df.dropna(how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     0    1    2   4\n",
      "0  1.0  6.5  3.0 NaN\n",
      "1  1.0  NaN  NaN NaN\n",
      "2  NaN  NaN  NaN NaN\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2\n",
       "0  1.0  6.5  3.0\n",
       "1  1.0  NaN  NaN\n",
       "2  NaN  NaN  NaN"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also column-wise drop instead of row-wise\n",
    "\n",
    "df[4] = np.nan\n",
    "print(df)\n",
    "df.dropna(axis=1, how='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0\n",
       "0  1.0\n",
       "1  1.0\n",
       "2  NaN"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also set a threshold for how many NAs in a row or column before you drop\n",
    "\n",
    "df.dropna(axis = 1, thresh = 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filling Null Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2    4\n",
       "0  1.0  6.5  3.0  0.0\n",
       "1  1.0  0.0  0.0  0.0\n",
       "2  0.0  0.0  0.0  0.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also fill in for nulls using fillna()\n",
    "\n",
    "df.fillna(0)"
   ]
  },
  {
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2    4\n",
       "0  1.0  6.5  3.0 -4.0\n",
       "1  1.0 -1.0 -2.0 -4.0\n",
       "2  0.0 -1.0 -2.0 -4.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# if you pass a dict, it fills different values for each column or row (depending on what axis you specified)\n",
    "\n",
    "df.fillna({0: 0, 1: -1, 2: -2, 4:-4})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2    4\n",
       "0  1.0  6.5  3.0  3.0\n",
       "1  1.0  1.0  1.0  NaN\n",
       "2  NaN  NaN  NaN  NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also use the same interpolation methods as with reindexing, like ffill and bfill, by specifying in 'method' arg\n",
    "\n",
    "df.fillna(axis = 1, method = 'ffill', limit = 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          0         1         2\n",
      "0 -1.209624 -0.279349 -0.840154\n",
      "1 -1.128682  0.565780 -0.834819\n",
      "2 -0.225752       NaN -0.389487\n",
      "3  1.129788       NaN  0.225419\n",
      "4 -0.991534       NaN       NaN\n",
      "5 -1.508194       NaN       NaN\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-1.209624</td>\n",
       "      <td>-0.279349</td>\n",
       "      <td>-0.840154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1.128682</td>\n",
       "      <td>0.565780</td>\n",
       "      <td>-0.834819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.225752</td>\n",
       "      <td>0.143215</td>\n",
       "      <td>-0.389487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.129788</td>\n",
       "      <td>0.143215</td>\n",
       "      <td>0.225419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.991534</td>\n",
       "      <td>0.143215</td>\n",
       "      <td>-0.459760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-1.508194</td>\n",
       "      <td>0.143215</td>\n",
       "      <td>-0.459760</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2\n",
       "0 -1.209624 -0.279349 -0.840154\n",
       "1 -1.128682  0.565780 -0.834819\n",
       "2 -0.225752  0.143215 -0.389487\n",
       "3  1.129788  0.143215  0.225419\n",
       "4 -0.991534  0.143215 -0.459760\n",
       "5 -1.508194  0.143215 -0.459760"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also get creative and use things like mean\n",
    "\n",
    "df = pd.DataFrame(np.random.randn(6, 3))\n",
    "df.iloc[2:, 1] = np.nan\n",
    "df.iloc[4:, 2] = np.nan\n",
    "print(df)\n",
    "\n",
    "df.fillna(df.mean())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Transformation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Removing Duplicate Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    k1  k2\n",
      "0  one   1\n",
      "1  two   1\n",
      "2  one   2\n",
      "3  two   3\n",
      "4  one   3\n",
      "5  two   4\n",
      "6  two   4\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2    False\n",
       "3    False\n",
       "4    False\n",
       "5    False\n",
       "6     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# duplicated() returns whether each row is a duplicate or not\n",
    "\n",
    "data = pd.DataFrame({'k1': ['one', 'two'] * 3 + ['two'], 'k2': [1,1,2,3,3,4,4]})\n",
    "print(data)\n",
    "data.duplicated()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>k1</th>\n",
       "      <th>k2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>one</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>two</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>one</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>two</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    k1  k2\n",
       "0  one   1\n",
       "1  two   1\n",
       "2  one   2\n",
       "3  two   3\n",
       "4  one   3\n",
       "5  two   4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop_duplicates returns a DF with only the rows where duplicated() returns false\n",
    "\n",
    "data.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    k1  k2  v1\n",
      "0  one   1   0\n",
      "1  two   1   1\n",
      "2  one   2   2\n",
      "3  two   3   3\n",
      "4  one   3   4\n",
      "5  two   4   5\n",
      "6  two   4   6\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>k1</th>\n",
       "      <th>k2</th>\n",
       "      <th>v1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    k1  k2  v1\n",
       "0  one   1   0\n",
       "1  two   1   1"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also select a specific column or an array of columns if you only want a more fine-grained filter\n",
    "\n",
    "data['v1'] = range(7)\n",
    "print(data)\n",
    "data.drop_duplicates(['k1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>k1</th>\n",
       "      <th>k2</th>\n",
       "      <th>v1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>one</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>two</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>one</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>two</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    k1  k2  v1\n",
       "0  one   1   0\n",
       "1  two   1   1\n",
       "2  one   2   2\n",
       "3  two   3   3\n",
       "4  one   3   4\n",
       "6  two   4   6"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# By default, these methods return the first observed value. Pass keep=last argument to return the last one instead\n",
    "\n",
    "data.drop_duplicates(['k1', 'k2'], keep='last')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Transform using a mapping or a function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>carat</th>\n",
       "      <th>cut</th>\n",
       "      <th>color</th>\n",
       "      <th>clarity</th>\n",
       "      <th>depth</th>\n",
       "      <th>table</th>\n",
       "      <th>price</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>z</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Ideal</td>\n",
       "      <td>E</td>\n",
       "      <td>SI2</td>\n",
       "      <td>61.5</td>\n",
       "      <td>55.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.95</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.21</td>\n",
       "      <td>Premium</td>\n",
       "      <td>E</td>\n",
       "      <td>SI1</td>\n",
       "      <td>59.8</td>\n",
       "      <td>61.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.89</td>\n",
       "      <td>3.84</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Good</td>\n",
       "      <td>E</td>\n",
       "      <td>VS1</td>\n",
       "      <td>56.9</td>\n",
       "      <td>65.0</td>\n",
       "      <td>327</td>\n",
       "      <td>4.05</td>\n",
       "      <td>4.07</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.29</td>\n",
       "      <td>Premium</td>\n",
       "      <td>I</td>\n",
       "      <td>VS2</td>\n",
       "      <td>62.4</td>\n",
       "      <td>58.0</td>\n",
       "      <td>334</td>\n",
       "      <td>4.20</td>\n",
       "      <td>4.23</td>\n",
       "      <td>2.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.31</td>\n",
       "      <td>Good</td>\n",
       "      <td>J</td>\n",
       "      <td>SI2</td>\n",
       "      <td>63.3</td>\n",
       "      <td>58.0</td>\n",
       "      <td>335</td>\n",
       "      <td>4.34</td>\n",
       "      <td>4.35</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.24</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>J</td>\n",
       "      <td>VVS2</td>\n",
       "      <td>62.8</td>\n",
       "      <td>57.0</td>\n",
       "      <td>336</td>\n",
       "      <td>3.94</td>\n",
       "      <td>3.96</td>\n",
       "      <td>2.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.24</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>I</td>\n",
       "      <td>VVS1</td>\n",
       "      <td>62.3</td>\n",
       "      <td>57.0</td>\n",
       "      <td>336</td>\n",
       "      <td>3.95</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.26</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>H</td>\n",
       "      <td>SI1</td>\n",
       "      <td>61.9</td>\n",
       "      <td>55.0</td>\n",
       "      <td>337</td>\n",
       "      <td>4.07</td>\n",
       "      <td>4.11</td>\n",
       "      <td>2.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.22</td>\n",
       "      <td>Fair</td>\n",
       "      <td>E</td>\n",
       "      <td>VS2</td>\n",
       "      <td>65.1</td>\n",
       "      <td>61.0</td>\n",
       "      <td>337</td>\n",
       "      <td>3.87</td>\n",
       "      <td>3.78</td>\n",
       "      <td>2.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>H</td>\n",
       "      <td>VS1</td>\n",
       "      <td>59.4</td>\n",
       "      <td>61.0</td>\n",
       "      <td>338</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.05</td>\n",
       "      <td>2.39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   carat        cut color clarity  depth  table  price     x     y     z\n",
       "0   0.23      Ideal     E     SI2   61.5   55.0    326  3.95  3.98  2.43\n",
       "1   0.21    Premium     E     SI1   59.8   61.0    326  3.89  3.84  2.31\n",
       "2   0.23       Good     E     VS1   56.9   65.0    327  4.05  4.07  2.31\n",
       "3   0.29    Premium     I     VS2   62.4   58.0    334  4.20  4.23  2.63\n",
       "4   0.31       Good     J     SI2   63.3   58.0    335  4.34  4.35  2.75\n",
       "5   0.24  Very Good     J    VVS2   62.8   57.0    336  3.94  3.96  2.48\n",
       "6   0.24  Very Good     I    VVS1   62.3   57.0    336  3.95  3.98  2.47\n",
       "7   0.26  Very Good     H     SI1   61.9   55.0    337  4.07  4.11  2.53\n",
       "8   0.22       Fair     E     VS2   65.1   61.0    337  3.87  3.78  2.49\n",
       "9   0.23  Very Good     H     VS1   59.4   61.0    338  4.00  4.05  2.39"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load sample dataset\n",
    "\n",
    "diamonds = sns.load_dataset('diamonds').head(10)\n",
    "diamonds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    superduper\n",
       "1         super\n",
       "2           yay\n",
       "3         super\n",
       "4           yay\n",
       "5           meh\n",
       "6           meh\n",
       "7           meh\n",
       "8       really?\n",
       "9           meh\n",
       "Name: cut, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can update column values by using the map() function - substitutes based on a dict\n",
    "\n",
    "newNames = ['SuperDuper', 'Super', 'Yay', 'Meh', 'Really?']\n",
    "mapping = dict(zip(diamonds['cut'].unique(), newNames))\n",
    "diamonds['cut'].map(lambda x: mapping[x].lower())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        IDEAL\n",
       "1      PREMIUM\n",
       "2         GOOD\n",
       "3      PREMIUM\n",
       "4         GOOD\n",
       "5    VERY GOOD\n",
       "6    VERY GOOD\n",
       "7    VERY GOOD\n",
       "8         FAIR\n",
       "9    VERY GOOD\n",
       "Name: cut, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Same idea but for a function, use apply()\n",
    "\n",
    "diamonds['cut'].apply(lambda x:x.upper())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Replacing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0       I deal\n",
      "1      Premium\n",
      "2         Good\n",
      "3      Premium\n",
      "4         Good\n",
      "5    Very Good\n",
      "6    Very Good\n",
      "7    Very Good\n",
      "8         Fair\n",
      "9    Very Good\n",
      "Name: cut, dtype: object\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0        Super\n",
       "1      Premium\n",
       "2         Good\n",
       "3      Premium\n",
       "4         Good\n",
       "5    Very Good\n",
       "6    Very Good\n",
       "7    Very Good\n",
       "8         Lame\n",
       "9    Very Good\n",
       "Name: cut, dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can change all instances of a given value with the replace() method. Like find & replace in Word or Excel\n",
    "# Can also pass in a dict to make multiple replacements\n",
    "\n",
    "print(diamonds['cut'].replace('Ideal', 'I deal'))\n",
    "diamonds['cut'].replace({'Ideal': 'Super', 'Fair': 'Lame'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Rename Axis Indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.DataFrame(np.arange(12).reshape((3, 4)), \n",
    "                    index= ['Ohio', 'Colorado', 'New York'], \n",
    "                    columns = ['one', 'two', 'three', 'four'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['OHIO', 'COLORADO', 'NEW YORK'], dtype='object')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Like Series, axis indexes have a map method (returns a copy, can also use inplace to modify the original)\n",
    "\n",
    "data.index.map(lambda x: x.upper())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>One</th>\n",
       "      <th>Two</th>\n",
       "      <th>Three</th>\n",
       "      <th>Four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>OHIO</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COLORADO</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NEW YORK</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          One  Two  Three  Four\n",
       "OHIO        0    1      2     3\n",
       "COLORADO    4    5      6     7\n",
       "NEW YORK    8    9     10    11"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If you want to create a transformed version of the dataset without modifying original, use rename() \n",
    "# Note, not an index method but one you call on the DataFrame itself\n",
    "\n",
    "data.rename(index=str.upper, columns=str.title)"
   ]
  },
  {
   "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></th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>peekaboo</th>\n",
       "      <th>four</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Indiana</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          one  two  peekaboo  four\n",
       "Indiana     0    1         2     3\n",
       "Colorado    4    5         6     7\n",
       "New York    8    9        10    11"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also use rename() and pass in a dict to only change a subset\n",
    "\n",
    "data.rename(index={'Ohio': 'Indiana'}, columns={'three': 'peekaboo'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Discretization and Binning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(18, 25], (18, 25], (18, 25], (25, 35], (18, 25], ..., (25, 35], (60, 100], (35, 60], (35, 60], (25, 35]]\n",
       "Length: 12\n",
       "Categories (4, interval[int64]): [(18, 25] < (25, 35] < (35, 60] < (60, 100]]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ages = [20, 22, 25, 27, 21, 23, 37, 31, 61, 45, 41, 32]\n",
    "\n",
    "# Let's say we want to divide into groups from 18-25, 26-35, 36-60, and 60-100\n",
    "\n",
    "# First we create an array with our dividing lines\n",
    "bins = [18, 25, 35, 60, 100]\n",
    "\n",
    "# Then we use the cut() function to divide up the data according to our brackets\n",
    "cats = pd.cut(ages, bins)\n",
    "cats\n",
    "\n",
    "# By default, the ranges are inclusive. To change pass right/left = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "IntervalIndex([(18, 25], (25, 35], (35, 60], (60, 100]],\n",
      "              closed='right',\n",
      "              dtype='interval[int64]')\n",
      "\n",
      "\n",
      "[0 0 0 1 0 0 2 1 3 2 2 1]\n"
     ]
    }
   ],
   "source": [
    "# Pandas returns a special object called a Categorical \n",
    "\n",
    "print(cats.categories)\n",
    "print('\\n')\n",
    "print(cats.codes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(18, 25]     5\n",
       "(25, 35]     3\n",
       "(35, 60]     3\n",
       "(60, 100]    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get bin counts from cut using value_counts()\n",
    "cats.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Youth, Youth, Youth, Young Adult, Youth, ..., Young Adult, Senior, Middle-Aged, Middle-Aged, Young Adult]\n",
       "Length: 12\n",
       "Categories (4, object): [Youth < Young Adult < Middle-Aged < Senior]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also pass in custom bin names using the labels argument\n",
    "\n",
    "group_names = ['Youth', 'Young Adult', 'Middle-Aged', 'Senior']\n",
    "pd.cut(ages, bins, labels=group_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(0.019, 0.26], (0.5, 0.74], (0.26, 0.5], (0.74, 0.98], (0.74, 0.98], ..., (0.019, 0.26], (0.5, 0.74], (0.26, 0.5], (0.5, 0.74], (0.5, 0.74]]\n",
       "Length: 20\n",
       "Categories (4, interval[float64]): [(0.019, 0.26] < (0.26, 0.5] < (0.5, 0.74] < (0.74, 0.98]]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Passing in an integer number will compute equal length bins based on the max and min values of the data\n",
    "# This works well for uniformly distributed data but won't help elsewhere in many cases\n",
    "# Precision arg determines number of decimal places to account for\n",
    "\n",
    "data = np.random.rand(20)\n",
    "pd.cut(data, 4, precision = 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.669, 3.317]       250\n",
       "(0.00546, 0.669]     250\n",
       "(-0.628, 0.00546]    250\n",
       "(-3.288, -0.628]     250\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Similarly, qcut will bin data based on sample quartiles according to the distribution\n",
    "# So for other distributions besides uniform, the bins should be roughply equal\n",
    "\n",
    "data = np.random.randn(1000)\n",
    "\n",
    "# Cut into quartiles\n",
    "cats = pd.qcut(data, 4)\n",
    "pd.value_counts(cats)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Detecting and Filtering Outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>-0.026185</td>\n",
       "      <td>-0.022451</td>\n",
       "      <td>-0.042255</td>\n",
       "      <td>0.035250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.008597</td>\n",
       "      <td>1.014185</td>\n",
       "      <td>1.059786</td>\n",
       "      <td>1.006739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-3.377399</td>\n",
       "      <td>-2.925890</td>\n",
       "      <td>-3.298367</td>\n",
       "      <td>-3.743725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>-0.722611</td>\n",
       "      <td>-0.690691</td>\n",
       "      <td>-0.746076</td>\n",
       "      <td>-0.638689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.011780</td>\n",
       "      <td>-0.008644</td>\n",
       "      <td>-0.042594</td>\n",
       "      <td>0.046178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.684065</td>\n",
       "      <td>0.655939</td>\n",
       "      <td>0.649242</td>\n",
       "      <td>0.739544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.798753</td>\n",
       "      <td>2.737621</td>\n",
       "      <td>3.262936</td>\n",
       "      <td>2.811124</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 0            1            2            3\n",
       "count  1000.000000  1000.000000  1000.000000  1000.000000\n",
       "mean     -0.026185    -0.022451    -0.042255     0.035250\n",
       "std       1.008597     1.014185     1.059786     1.006739\n",
       "min      -3.377399    -2.925890    -3.298367    -3.743725\n",
       "25%      -0.722611    -0.690691    -0.746076    -0.638689\n",
       "50%       0.011780    -0.008644    -0.042594     0.046178\n",
       "75%       0.684065     0.655939     0.649242     0.739544\n",
       "max       2.798753     2.737621     3.262936     2.811124"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame(np.random.randn(1000, 4))\n",
    "data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>405</th>\n",
       "      <td>-0.242326</td>\n",
       "      <td>-0.598379</td>\n",
       "      <td>1.299845</td>\n",
       "      <td>-3.217050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>-0.710589</td>\n",
       "      <td>-0.489917</td>\n",
       "      <td>-0.461268</td>\n",
       "      <td>-3.743725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>581</th>\n",
       "      <td>-3.377399</td>\n",
       "      <td>-0.127976</td>\n",
       "      <td>1.113611</td>\n",
       "      <td>1.302520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>762</th>\n",
       "      <td>1.338895</td>\n",
       "      <td>1.106175</td>\n",
       "      <td>3.178926</td>\n",
       "      <td>-1.268333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>815</th>\n",
       "      <td>-1.772872</td>\n",
       "      <td>-0.319205</td>\n",
       "      <td>3.262936</td>\n",
       "      <td>0.425797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845</th>\n",
       "      <td>0.818638</td>\n",
       "      <td>-0.495861</td>\n",
       "      <td>-3.298367</td>\n",
       "      <td>-0.017857</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            0         1         2         3\n",
       "405 -0.242326 -0.598379  1.299845 -3.217050\n",
       "543 -0.710589 -0.489917 -0.461268 -3.743725\n",
       "581 -3.377399 -0.127976  1.113611  1.302520\n",
       "762  1.338895  1.106175  3.178926 -1.268333\n",
       "815 -1.772872 -0.319205  3.262936  0.425797\n",
       "845  0.818638 -0.495861 -3.298367 -0.017857"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can use the any() method to find rows/columns where any value meets a given condition\n",
    "\n",
    "data[(np.abs(data) > 3).any(axis = 1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    0.493222\n",
       "1    0.379464\n",
       "2   -1.468208\n",
       "3    0.083983\n",
       "Name: 860, dtype: float64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also set values this way, e.g. here's how you would cap values outside of the interval\n",
    "\n",
    "data[np.abs(data) > 3] = np.sign(data) * 3\n",
    "data.iloc[860]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Permutation and Random Sampling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    0   1   2   3\n",
      "0   0   1   2   3\n",
      "1   4   5   6   7\n",
      "2   8   9  10  11\n",
      "3  12  13  14  15\n",
      "4  16  17  18  19\n",
      "\n",
      "\n",
      "[3 1 4 2 0]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    0   1   2   3\n",
       "3  12  13  14  15\n",
       "1   4   5   6   7\n",
       "4  16  17  18  19\n",
       "2   8   9  10  11\n",
       "0   0   1   2   3"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Numpy has a method for random re-ordering called permuation()\n",
    "\n",
    "df = pd.DataFrame(np.arange(5 * 4).reshape((5,4)))\n",
    "print(df)\n",
    "print('\\n')\n",
    "sampler = np.random.permutation(5) # Returns a range of 0-4 elements in a random order\n",
    "print(sampler)\n",
    "\n",
    "df.take(sampler)\n",
    "# df.iloc[sampler] is equivalent\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    0   1   2   3\n",
       "2   8   9  10  11\n",
       "3  12  13  14  15\n",
       "1   4   5   6   7"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Take a random subset using the sample method\n",
    "\n",
    "df.sample(n=3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    5\n",
      "1    7\n",
      "2   -1\n",
      "3    6\n",
      "4    4\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    5\n",
       "0    5\n",
       "2   -1\n",
       "3    6\n",
       "0    5\n",
       "4    4\n",
       "3    6\n",
       "0    5\n",
       "0    5\n",
       "3    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can enable replace = True to allow for repeat choices\n",
    "\n",
    "choices = pd.Series([5, 7, -1, 6, 4])\n",
    "print(choices)\n",
    "draws = choices.sample(n=10, replace=True)\n",
    "draws"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Computing Dummy/Indicator Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0        Ideal\n",
      "1      Premium\n",
      "2         Good\n",
      "3      Premium\n",
      "4         Good\n",
      "5    Very Good\n",
      "6    Very Good\n",
      "7    Very Good\n",
      "8         Fair\n",
      "9    Very Good\n",
      "Name: cut, dtype: object\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fair</th>\n",
       "      <th>Good</th>\n",
       "      <th>Ideal</th>\n",
       "      <th>Premium</th>\n",
       "      <th>Very Good</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Fair  Good  Ideal  Premium  Very Good\n",
       "0     0     0      1        0          0\n",
       "1     0     0      0        1          0\n",
       "2     0     1      0        0          0\n",
       "3     0     0      0        1          0\n",
       "4     0     1      0        0          0\n",
       "5     0     0      0        0          1\n",
       "6     0     0      0        0          1\n",
       "7     0     0      0        0          1\n",
       "8     1     0      0        0          0\n",
       "9     0     0      0        0          1"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Common statistical or ML task is converting a categorical variable into a binary indicator matrix\n",
    "print(diamonds['cut'])\n",
    "pd.get_dummies(diamonds['cut'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   key_SI1  key_SI2  key_VS1  key_VS2  key_VVS1  key_VVS2  key_Fair  key_Good  \\\n",
      "0        0        1        0        0         0         0         0         0   \n",
      "1        1        0        0        0         0         0         0         0   \n",
      "2        0        0        1        0         0         0         0         1   \n",
      "3        0        0        0        1         0         0         0         0   \n",
      "4        0        1        0        0         0         0         0         1   \n",
      "5        0        0        0        0         0         1         0         0   \n",
      "6        0        0        0        0         1         0         0         0   \n",
      "7        1        0        0        0         0         0         0         0   \n",
      "8        0        0        0        1         0         0         1         0   \n",
      "9        0        0        1        0         0         0         0         0   \n",
      "\n",
      "   key_Ideal  key_Premium  key_Very Good  \n",
      "0          1            0              0  \n",
      "1          0            1              0  \n",
      "2          0            0              0  \n",
      "3          0            1              0  \n",
      "4          0            0              0  \n",
      "5          0            0              1  \n",
      "6          0            0              1  \n",
      "7          0            0              1  \n",
      "8          0            0              0  \n",
      "9          0            0              1  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>clarity</th>\n",
       "      <th>cut</th>\n",
       "      <th>key_SI1</th>\n",
       "      <th>key_SI2</th>\n",
       "      <th>key_VS1</th>\n",
       "      <th>key_VS2</th>\n",
       "      <th>key_VVS1</th>\n",
       "      <th>key_VVS2</th>\n",
       "      <th>key_Fair</th>\n",
       "      <th>key_Good</th>\n",
       "      <th>key_Ideal</th>\n",
       "      <th>key_Premium</th>\n",
       "      <th>key_Very Good</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SI2</td>\n",
       "      <td>Ideal</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SI1</td>\n",
       "      <td>Premium</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>VS1</td>\n",
       "      <td>Good</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>VS2</td>\n",
       "      <td>Premium</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SI2</td>\n",
       "      <td>Good</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>VVS2</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>VVS1</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SI1</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>VS2</td>\n",
       "      <td>Fair</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>VS1</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  clarity        cut  key_SI1  key_SI2  key_VS1  key_VS2  key_VVS1  key_VVS2  \\\n",
       "0     SI2      Ideal        0        1        0        0         0         0   \n",
       "1     SI1    Premium        1        0        0        0         0         0   \n",
       "2     VS1       Good        0        0        1        0         0         0   \n",
       "3     VS2    Premium        0        0        0        1         0         0   \n",
       "4     SI2       Good        0        1        0        0         0         0   \n",
       "5    VVS2  Very Good        0        0        0        0         0         1   \n",
       "6    VVS1  Very Good        0        0        0        0         1         0   \n",
       "7     SI1  Very Good        1        0        0        0         0         0   \n",
       "8     VS2       Fair        0        0        0        1         0         0   \n",
       "9     VS1  Very Good        0        0        1        0         0         0   \n",
       "\n",
       "   key_Fair  key_Good  key_Ideal  key_Premium  key_Very Good  \n",
       "0         0         0          1            0              0  \n",
       "1         0         0          0            1              0  \n",
       "2         0         1          0            0              0  \n",
       "3         0         0          0            1              0  \n",
       "4         0         1          0            0              0  \n",
       "5         0         0          0            0              1  \n",
       "6         0         0          0            0              1  \n",
       "7         0         0          0            0              1  \n",
       "8         1         0          0            0              0  \n",
       "9         0         0          0            0              1  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also add a prefix for easy joining later\n",
    "\n",
    "cuts = diamonds[['clarity', 'cut']]\n",
    "dummies = pd.get_dummies(cuts, prefix='key')\n",
    "print(dummies)\n",
    "cuts.join(dummies)"
   ]
  },
  {
   "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></th>\n",
       "      <th>(0.0, 0.2]</th>\n",
       "      <th>(0.2, 0.4]</th>\n",
       "      <th>(0.4, 0.6]</th>\n",
       "      <th>(0.6, 0.8]</th>\n",
       "      <th>(0.8, 1.0]</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   (0.0, 0.2]  (0.2, 0.4]  (0.4, 0.6]  (0.6, 0.8]  (0.8, 1.0]\n",
       "0           0           0           0           0           1\n",
       "1           0           1           0           0           0\n",
       "2           1           0           0           0           0\n",
       "3           0           1           0           0           0\n",
       "4           0           0           1           0           0\n",
       "5           0           0           1           0           0\n",
       "6           0           0           0           0           1\n",
       "7           0           0           0           1           0\n",
       "8           0           0           0           1           0\n",
       "9           0           0           0           1           0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# A useful statistical recipe is to combine get_dummies with cut() to discretize\n",
    "\n",
    "np.random.seed(12345)\n",
    "values = np.random.rand(10)\n",
    "\n",
    "values\n",
    "\n",
    "bins = [0, 0.2, 0.4, 0.6, 0.8, 1]\n",
    "\n",
    "pd.get_dummies(pd.cut(values, bins))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### String Manipulations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['a', 'b', ' guido']"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# split a string based on a delimiter\n",
    "\n",
    "val = 'a,b, guido'\n",
    "val.split(',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['a', 'b', 'guido']"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# strip to trim whitespace\n",
    "\n",
    "[x.strip() for x in val.split(',')]\n",
    "\n",
    "# lstrip/rstrip do one side or the other"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'a::b::guido'"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# can join the pieces back together with a new delimter using join()\n",
    "\n",
    "pieces = [x.strip() for x in val.split(',')]\n",
    "'::'.join(pieces)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# finding substrings using the 'in' keyword\n",
    "\n",
    "'guido' in val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5\n",
      "5\n"
     ]
    }
   ],
   "source": [
    "# Can also use index and find methods to get the index where the string starts\n",
    "\n",
    "print(val.find('guido'))\n",
    "print(val.index('guido'))\n",
    "\n",
    "# Note, if the string isn't found, index will return an exception vs. a -1 value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'a,b, dido'"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace\n",
    "\n",
    "val.replace('guido', 'dido')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count number of occurrences of substring\n",
    "\n",
    "text = 'store data in the form of rectangular grids by which the data can be over viewed easily. Each row of the rectangular grid contains values of an instance, and each column of the grid is a vector which holds data for a specific variable. This means that rows of a DataFrame do not need to contain, values of same data type, they can be numeric, character, logical, etc. DataFrames for Python come with the Pandas library, and they are defined as two-dimensional labeled data structures with potentially different types of columns.'\n",
    "\n",
    "text.count('data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "468"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rfind() finds the position of the first index of the last occurrence of the substring, vs. find which finds first\n",
    "\n",
    "print(text.find('data'))\n",
    "text.rfind('data')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Regular Expressions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python's built in module for handling regular expressions\n",
    "\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Regular expressions provide a flexible way to search or match for more complex patterns\n",
    "# More here: https://www.w3schools.com/python/python_regex.asp\n",
    "\n",
    "text = \"foo   bar\\t baz \\tqux\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['foo', 'bar', 'baz', 'qux']"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "re.split(r'\\s+', text) # split where there are any number of spaces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['foo', 'bar', 'baz', 'qux']"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can precompile for better performance\n",
    "\n",
    "regex = re.compile(r'\\s+')\n",
    "regex.split(text)\n",
    "# Prepend the r character to use a raw string literal to avoid unwanted escaping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "emails = \"\"\"Dave dave@google.com \n",
    "Steve steve@gmail.com \n",
    "Rob rob@gmail.com\n",
    "Ryan ryan@yahoo.com\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define the search pattern\n",
    "\n",
    "pattern = r'[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,4}'\n",
    "regex = re.compile(pattern, flags = re.IGNORECASE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['dave@google.com', 'steve@gmail.com', 'rob@gmail.com', 'ryan@yahoo.com']"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# findall() produces the full list of matches\n",
    "\n",
    "regex.findall(emails)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<_sre.SRE_Match object; span=(5, 20), match='dave@google.com'>"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# search returns a match object for the first email in the text and also the starting&ending positions of the pattern\n",
    "\n",
    "regex.search(emails)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('apruden', 'a16z', 'com')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[('dave', 'google', 'com'),\n",
       " ('steve', 'gmail', 'com'),\n",
       " ('rob', 'gmail', 'com'),\n",
       " ('ryan', 'yahoo', 'com')]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also structure the pattern slightly differently to break up the results into segments using ()\n",
    "\n",
    "pattern = r'([A-Z0-9._%+-]+)@([A-Z0-9.-]+)\\.([A-Z]{2,4})'\n",
    "regex = re.compile(pattern, flags = re.IGNORECASE)\n",
    "\n",
    "# match() has the groups() function which splits out the regex into its predefined constituents\n",
    "\n",
    "print(regex.match('apruden@a16z.com').groups())\n",
    "\n",
    "# findall() methods return a list of tuples when the regex is grouped with ()\n",
    "\n",
    "regex.findall(emails)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<_sre.SRE_Match object; span=(5, 20), match='dave@google.com'>\n",
      "<_sre.SRE_Match object; span=(28, 43), match='steve@gmail.com'>\n"
     ]
    }
   ],
   "source": [
    "# Can also use to return an iterator obj. See here for more details: \n",
    "# https://docs.python.org/3/tutorial/classes.html#iterators\n",
    "\n",
    "i = regex.finditer(emails)\n",
    "print(next(i))\n",
    "print(next(i))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Vectorized string methods (accessed using .str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rob       rob@yahoo.com\n",
       "Dave    dave@google.com\n",
       "Alex        ap@alex.com\n",
       "Mo                  NaN\n",
       "dtype: object"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex = pd.Series({'Rob': 'rob@yahoo.com', 'Dave': 'dave@google.com', 'Alex': 'ap@alex.com', 'Mo': np.nan})\n",
    "ex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rob      True\n",
       "Dave    False\n",
       "Alex    False\n",
       "Mo        NaN\n",
       "dtype: object"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# contains(): is substring in list (boolean)?\n",
    "\n",
    "ex.str.contains('yahoo')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rob      yahoo\n",
       "Dave    google\n",
       "Alex      alex\n",
       "Mo         NaN\n",
       "dtype: object"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# vectorized element retrieval using Regex'es\n",
    "\n",
    "pattern = r'([A-Z0-9._%+-]+)@([A-Z0-9.-]+)\\.([A-Z]{2,4})'\n",
    "regex = re.compile(pattern, flags = re.IGNORECASE )\n",
    "\n",
    "matches = ex.str.findall(regex).str[0]\n",
    "matches.str.get(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Rob     rob@y\n",
       "Dave    dave@\n",
       "Alex    ap@al\n",
       "Mo        NaN\n",
       "dtype: object"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Also possible to slice in a vectorized fashion\n",
    "\n",
    "ex.str[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Rob</th>\n",
       "      <td>rob</td>\n",
       "      <td>yahoo</td>\n",
       "      <td>com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dave</th>\n",
       "      <td>dave</td>\n",
       "      <td>google</td>\n",
       "      <td>com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alex</th>\n",
       "      <td>ap</td>\n",
       "      <td>alex</td>\n",
       "      <td>com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mo</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         0       1    2\n",
       "Rob    rob   yahoo  com\n",
       "Dave  dave  google  com\n",
       "Alex    ap    alex  com\n",
       "Mo     NaN     NaN  NaN"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract() pulls the above into it's own dataframe\n",
    "\n",
    "ex.str.extract(regex)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Wrangling"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Hierarchical Indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a  1    1.007189\n",
      "   2   -1.296221\n",
      "   3    0.274992\n",
      "b  1    0.228913\n",
      "   3    1.352917\n",
      "c  1    0.886429\n",
      "   2   -2.001637\n",
      "d  2   -0.371843\n",
      "   3    1.669025\n",
      "dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "MultiIndex([('a', 1),\n",
       "            ('a', 2),\n",
       "            ('a', 3),\n",
       "            ('b', 1),\n",
       "            ('b', 3),\n",
       "            ('c', 1),\n",
       "            ('c', 2),\n",
       "            ('d', 2),\n",
       "            ('d', 3)],\n",
       "           )"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Hierarchical indexing provides a way to work with higher-dimensional data in a two-dimensional form\n",
    "# Created by passing in an array of arrays to the index argument. First array is the top level of the hierarchy\n",
    "\n",
    "data = pd.Series(np.random.randn(9), index= [['a','a','a','b','b','c','c','d','d',],\n",
    "                                            [1,2,3,1,3,1,2,2,3]])\n",
    "print(data)\n",
    "\n",
    "data.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b  1    0.228913\n",
      "   3    1.352917\n",
      "c  1    0.886429\n",
      "   2   -2.001637\n",
      "dtype: float64\n",
      "\n",
      "\n",
      "b  1    0.228913\n",
      "   3    1.352917\n",
      "c  1    0.886429\n",
      "   2   -2.001637\n",
      "dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "a   -1.296221\n",
       "c   -2.001637\n",
       "d   -0.371843\n",
       "dtype: float64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can select using hierarchical index with same methods as a normal dataframe\n",
    "\n",
    "print(data['b':'c'])\n",
    "print('\\n')\n",
    "print(data.loc[['b','c']])\n",
    "\n",
    "# Select inner level\n",
    "data[:,2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          1         2         3\n",
      "a  1.007189 -1.296221  0.274992\n",
      "b  0.228913       NaN  1.352917\n",
      "c  0.886429 -2.001637       NaN\n",
      "d       NaN -0.371843  1.669025\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "a  1    1.007189\n",
       "   2   -1.296221\n",
       "   3    0.274992\n",
       "b  1    0.228913\n",
       "   3    1.352917\n",
       "c  1    0.886429\n",
       "   2   -2.001637\n",
       "d  2   -0.371843\n",
       "   3    1.669025\n",
       "dtype: float64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can rearrange the multiindex Series into a dataframe and back using unstack() and stack()\n",
    "\n",
    "print(data.unstack())\n",
    "data.unstack().stack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Ohio       Colorado\n",
       "     Red Green    Green\n",
       "a 1    0     1        2\n",
       "  2    3     4        5\n",
       "b 1    6     7        8\n",
       "  2    9    10       11"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Either axis of a DataFrame can have a hierarchical index\n",
    "\n",
    "frame = pd.DataFrame(np.arange(12).reshape((4,3)), \n",
    "                     index = [['a', 'a', 'b', 'b'], [1,2,1,2]], \n",
    "                     columns = [['Ohio', 'Ohio', 'Colorado'], ['Red', 'Green', 'Green']])\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></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>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state     Ohio       Colorado\n",
       "color      Red Green    Green\n",
       "key1 key2                    \n",
       "a    1       0     1        2\n",
       "     2       3     4        5\n",
       "b    1       6     7        8\n",
       "     2       9    10       11"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also name the index and column levels\n",
    "\n",
    "frame.index.names = ['key1', 'key2']\n",
    "frame.columns.names = ['state', 'color']\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>color</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "color      Green\n",
       "key1 key2       \n",
       "a    1         2\n",
       "     2         5\n",
       "b    1         8\n",
       "     2        11"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Column selection is the same as with a single index\n",
    "\n",
    "frame['Colorado']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MultiIndex([(    'Ohio',   'Red'),\n",
      "            (    'Ohio', 'Green'),\n",
      "            ('Colorado', 'Green')],\n",
      "           names=['state', 'color'])\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "MultiIndex([(    'Ohio', 'Green'),\n",
       "            (    'Ohio',   'Red'),\n",
       "            ('Colorado', 'Green')],\n",
       "           names=['state', 'color'])"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also create a multiindex from scratch\n",
    "\n",
    "print(frame.columns)\n",
    "\n",
    "pd.MultiIndex.from_arrays([['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']], names = ['state', 'color'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "state     Ohio       Colorado\n",
      "color      Red Green    Green\n",
      "key2 key1                    \n",
      "1    a       0     1        2\n",
      "2    a       3     4        5\n",
      "1    b       6     7        8\n",
      "2    b       9    10       11\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key2</th>\n",
       "      <th>key1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>a</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>a</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state     Ohio       Colorado\n",
       "color      Red Green    Green\n",
       "key2 key1                    \n",
       "1    a       0     1        2\n",
       "     b       6     7        8\n",
       "2    a       3     4        5\n",
       "     b       9    10       11"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also swap the levels of a hierarchical index by either passing in the new order you want\n",
    "\n",
    "print(frame.swaplevel('key1', 'key2')) # Equivalent to .swaplevel(0,1)\n",
    "\n",
    "# Also common to combine with sort_index to lexicographically sort by level\n",
    "\n",
    "frame.swaplevel('key1','key2', axis = 0).sort_index(level = 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Summary Statistics by Level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "state Ohio       Colorado\n",
      "color  Red Green    Green\n",
      "key2                     \n",
      "1        6     8       10\n",
      "2       12    14       16\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</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>1</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "color      Red  Green\n",
       "key1 key2            \n",
       "a    1       0      3\n",
       "     2       3      9\n",
       "b    1       6     15\n",
       "     2       9     21"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can select levels to aggregate summary statistics on a given axis\n",
    "\n",
    "print(frame.sum(level = 'key2'))\n",
    "\n",
    "frame.sum(level = 'color', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Setting a new DataFrame index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>carat</th>\n",
       "      <th>cut</th>\n",
       "      <th>color</th>\n",
       "      <th>depth</th>\n",
       "      <th>table</th>\n",
       "      <th>price</th>\n",
       "      <th>x</th>\n",
       "      <th>y</th>\n",
       "      <th>z</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>clarity</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>SI2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Ideal</td>\n",
       "      <td>E</td>\n",
       "      <td>61.5</td>\n",
       "      <td>55.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.95</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SI1</th>\n",
       "      <td>0.21</td>\n",
       "      <td>Premium</td>\n",
       "      <td>E</td>\n",
       "      <td>59.8</td>\n",
       "      <td>61.0</td>\n",
       "      <td>326</td>\n",
       "      <td>3.89</td>\n",
       "      <td>3.84</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VS1</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Good</td>\n",
       "      <td>E</td>\n",
       "      <td>56.9</td>\n",
       "      <td>65.0</td>\n",
       "      <td>327</td>\n",
       "      <td>4.05</td>\n",
       "      <td>4.07</td>\n",
       "      <td>2.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VS2</th>\n",
       "      <td>0.29</td>\n",
       "      <td>Premium</td>\n",
       "      <td>I</td>\n",
       "      <td>62.4</td>\n",
       "      <td>58.0</td>\n",
       "      <td>334</td>\n",
       "      <td>4.20</td>\n",
       "      <td>4.23</td>\n",
       "      <td>2.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SI2</th>\n",
       "      <td>0.31</td>\n",
       "      <td>Good</td>\n",
       "      <td>J</td>\n",
       "      <td>63.3</td>\n",
       "      <td>58.0</td>\n",
       "      <td>335</td>\n",
       "      <td>4.34</td>\n",
       "      <td>4.35</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VVS2</th>\n",
       "      <td>0.24</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>J</td>\n",
       "      <td>62.8</td>\n",
       "      <td>57.0</td>\n",
       "      <td>336</td>\n",
       "      <td>3.94</td>\n",
       "      <td>3.96</td>\n",
       "      <td>2.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VVS1</th>\n",
       "      <td>0.24</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>I</td>\n",
       "      <td>62.3</td>\n",
       "      <td>57.0</td>\n",
       "      <td>336</td>\n",
       "      <td>3.95</td>\n",
       "      <td>3.98</td>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SI1</th>\n",
       "      <td>0.26</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>H</td>\n",
       "      <td>61.9</td>\n",
       "      <td>55.0</td>\n",
       "      <td>337</td>\n",
       "      <td>4.07</td>\n",
       "      <td>4.11</td>\n",
       "      <td>2.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VS2</th>\n",
       "      <td>0.22</td>\n",
       "      <td>Fair</td>\n",
       "      <td>E</td>\n",
       "      <td>65.1</td>\n",
       "      <td>61.0</td>\n",
       "      <td>337</td>\n",
       "      <td>3.87</td>\n",
       "      <td>3.78</td>\n",
       "      <td>2.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VS1</th>\n",
       "      <td>0.23</td>\n",
       "      <td>Very Good</td>\n",
       "      <td>H</td>\n",
       "      <td>59.4</td>\n",
       "      <td>61.0</td>\n",
       "      <td>338</td>\n",
       "      <td>4.00</td>\n",
       "      <td>4.05</td>\n",
       "      <td>2.39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         carat        cut color  depth  table  price     x     y     z\n",
       "clarity                                                               \n",
       "SI2       0.23      Ideal     E   61.5   55.0    326  3.95  3.98  2.43\n",
       "SI1       0.21    Premium     E   59.8   61.0    326  3.89  3.84  2.31\n",
       "VS1       0.23       Good     E   56.9   65.0    327  4.05  4.07  2.31\n",
       "VS2       0.29    Premium     I   62.4   58.0    334  4.20  4.23  2.63\n",
       "SI2       0.31       Good     J   63.3   58.0    335  4.34  4.35  2.75\n",
       "VVS2      0.24  Very Good     J   62.8   57.0    336  3.94  3.96  2.48\n",
       "VVS1      0.24  Very Good     I   62.3   57.0    336  3.95  3.98  2.47\n",
       "SI1       0.26  Very Good     H   61.9   55.0    337  4.07  4.11  2.53\n",
       "VS2       0.22       Fair     E   65.1   61.0    337  3.87  3.78  2.49\n",
       "VS1       0.23  Very Good     H   59.4   61.0    338  4.00  4.05  2.39"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "diamonds.set_index('clarity') # Can leave original column in with the drop argument set to False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merging and Combining Datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge - database style join operations (can also use the more specific join() method to join by index)\n",
    "# concat - stacks together objects along an axis\n",
    "# combine_first method enables splicing together overlapping data to fill missing values from one to another"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1\n",
      "0   b      0\n",
      "1   b      1\n",
      "2   a      2\n",
      "3   c      3\n",
      "4   a      4\n",
      "5   a      5\n",
      "6   b      6\n",
      "\n",
      "\n",
      "  key  data2\n",
      "0   a      0\n",
      "1   b      1\n",
      "2   d      2\n"
     ]
    }
   ],
   "source": [
    "# Merge\n",
    "\n",
    "df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})\n",
    "df2 = pd.DataFrame({'key': ['a','b','d'], 'data2': range(3)})\n",
    "print(df1)\n",
    "print('\\n')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data1  data2\n",
       "0   b      0      1\n",
       "1   b      1      1\n",
       "2   b      6      1\n",
       "3   a      2      0\n",
       "4   a      4      0\n",
       "5   a      5      0"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge\n",
    "\n",
    "pd.merge(df1, df2, on='key') # specify column on which to join, pandas can usually handle w/o but good to specify"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data1  data2\n",
       "0   b    0.0      1\n",
       "1   b    1.0      1\n",
       "2   b    6.0      1\n",
       "3   a    2.0      0\n",
       "4   a    4.0      0\n",
       "5   a    5.0      0\n",
       "6   d    NaN      2"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Default join method is inner, but can also specify left, right, and outer\n",
    "\n",
    "pd.merge(df1, df2, how='right', on='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>lkey</th>\n",
       "      <th>data1</th>\n",
       "      <th>rkey</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>6</td>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>4</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a</td>\n",
       "      <td>5</td>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  lkey  data1 rkey  data2\n",
       "0    b      0    b      1\n",
       "1    b      1    b      1\n",
       "2    b      6    b      1\n",
       "3    a      2    a      0\n",
       "4    a      4    a      0\n",
       "5    a      5    a      0"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If column names are different for each object, then you can specify them separately\n",
    "\n",
    "df1 = pd.DataFrame({'lkey': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})\n",
    "df2 = pd.DataFrame({'rkey': ['a','b','d'], 'data2': range(3)})\n",
    "\n",
    "pd.merge(df1, df2, left_on='lkey', right_on='rkey')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "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>lval</th>\n",
       "      <th>rval</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bar</td>\n",
       "      <td>two</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2  lval  rval      _merge\n",
       "0  foo  one   1.0   4.0        both\n",
       "1  foo  one   1.0   5.0        both\n",
       "2  foo  two   2.0   NaN   left_only\n",
       "3  bar  one   3.0   6.0        both\n",
       "4  bar  two   NaN   7.0  right_only"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Multi-index join with multiple keys\n",
    "\n",
    "left = pd.DataFrame({'key1': ['foo', 'foo', 'bar'], 'key2': ['one', 'two', 'one'], 'lval': [1,2,3]})\n",
    "right = pd.DataFrame({'key1': ['foo', 'foo', 'bar','bar'], 'key2': ['one', 'one', 'one', 'two'], 'rval': [4,5,6,7]})\n",
    "\n",
    "pd.merge(left, right, on=['key1', 'key2'], how='outer', indicator=True)\n",
    "\n",
    "# indicator argument tells you which side of the join that row came from"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key1 key2_x  lval key2_y  rval\n",
      "0  foo    one     1    one     4\n",
      "1  foo    one     1    one     5\n",
      "2  foo    two     2    one     4\n",
      "3  foo    two     2    one     5\n",
      "4  bar    one     3    one     6\n",
      "5  bar    one     3    two     7\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2_left</th>\n",
       "      <th>lval</th>\n",
       "      <th>key2_right</th>\n",
       "      <th>rval</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>one</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>2</td>\n",
       "      <td>one</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>2</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>3</td>\n",
       "      <td>one</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>3</td>\n",
       "      <td>two</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2_left  lval key2_right  rval\n",
       "0  foo       one     1        one     4\n",
       "1  foo       one     1        one     5\n",
       "2  foo       two     2        one     4\n",
       "3  foo       two     2        one     5\n",
       "4  bar       one     3        one     6\n",
       "5  bar       one     3        two     7"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also use the suffix argument to specify suffixes to append when there are overlapping names \n",
    "\n",
    "# Default behavior, distinguishes l/r using x/y\n",
    "print(pd.merge(left, right, on='key1'))\n",
    "\n",
    "# Using suffix arg\n",
    "pd.merge(left, right, on='key1', suffixes=('_left', '_right'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merging on Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  value\n",
      "0   a      0\n",
      "1   b      1\n",
      "2   a      2\n",
      "3   a      3\n",
      "4   b      4\n",
      "5   c      5\n",
      "\n",
      "\n",
      "   group_val\n",
      "a        3.5\n",
      "b        7.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>value</th>\n",
       "      <th>group_val</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>0</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>3.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>c</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  value  group_val\n",
       "0   a      0        3.5\n",
       "2   a      2        3.5\n",
       "3   a      3        3.5\n",
       "1   b      1        7.0\n",
       "4   b      4        7.0\n",
       "5   c      5        NaN"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# In some cases, merge key(s) in the DF will be in the index. Pass left_index/right_index = True to handle that case\n",
    "\n",
    "left1 = pd.DataFrame({'key': ['a', 'b', 'a', 'a', 'b', 'c'], 'value': range(6)})\n",
    "right1 = pd.DataFrame({'group_val': [3.5, 7]}, index = ['a', 'b'])\n",
    "print(left1)\n",
    "print('\\n')\n",
    "print(right1)\n",
    "\n",
    "pd.merge(left1, right1, left_on='key', right_index = True, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     key1  key2  data\n",
      "0    Ohio  2000   0.0\n",
      "1    Ohio  2001   1.0\n",
      "2  Nevada  2002   2.0\n",
      "\n",
      "\n",
      "             event1  event2\n",
      "Nevada 2000       0       1\n",
      "Ohio   2000       2       3\n",
      "       2001       4       5\n",
      "       2002       6       7\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>data</th>\n",
       "      <th>event1</th>\n",
       "      <th>event2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2001</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>2002</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>2000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     key1  key2  data  event1  event2\n",
       "0    Ohio  2000   0.0     2.0     3.0\n",
       "1    Ohio  2001   1.0     4.0     5.0\n",
       "2  Nevada  2002   2.0     NaN     NaN\n",
       "2  Nevada  2000   NaN     0.0     1.0\n",
       "2    Ohio  2002   NaN     6.0     7.0"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Joining a hierachically indexed DF is tricker b/c joining on index is implicitly a multi-key merge\n",
    "\n",
    "lefth = pd.DataFrame({'key1': ['Ohio', 'Ohio', 'Nevada'],\n",
    "                      'key2': ['2000', '2001', '2002'], \n",
    "                     'data': np.arange(3.)})\n",
    "\n",
    "righth = pd.DataFrame(np.arange(8).reshape((4,2)), \n",
    "                      index=[['Nevada', 'Ohio', 'Ohio', 'Ohio'], ['2000', '2000', '2001', '2002']], \n",
    "                     columns = ['event1', 'event2'])\n",
    "print(lefth)\n",
    "print('\\n')\n",
    "print(righth)\n",
    "\n",
    "# Pass multiple columns to merge on as a list\n",
    "\n",
    "pd.merge(lefth, righth, left_on=['key1', 'key2'], right_index = True, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Ohio  Nevada\n",
      "a   1.0     2.0\n",
      "c   3.0     4.0\n",
      "e   5.0     6.0\n",
      "\n",
      "\n",
      "   Missouri  Texas\n",
      "b       7.0    8.0\n",
      "c       9.0   10.0\n",
      "d      11.0   12.0\n",
      "e      13.0   14.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Nevada</th>\n",
       "      <th>Missouri</th>\n",
       "      <th>Texas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Ohio  Nevada  Missouri  Texas\n",
       "a   1.0     2.0       NaN    NaN\n",
       "b   NaN     NaN       7.0    8.0\n",
       "c   3.0     4.0       9.0   10.0\n",
       "d   NaN     NaN      11.0   12.0\n",
       "e   5.0     6.0      13.0   14.0"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also use the indexes of both sides to merge\n",
    "\n",
    "left2 = pd.DataFrame([[1., 2.], [3.,4.], [5.,6.]], index = list('ace'), columns=['Ohio', 'Nevada'])\n",
    "right2 = pd.DataFrame([[7.,8.], [9.,10.], [11.,12.], [13.,14.]], index=list('bcde'), columns=['Missouri', 'Texas'])\n",
    "\n",
    "print(left2)\n",
    "print('\\n')\n",
    "print(right2)\n",
    "\n",
    "pd.merge(left2, right2, left_index = True, right_index = True, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>Ohio</th>\n",
       "      <th>Nevada</th>\n",
       "      <th>Missouri</th>\n",
       "      <th>Texas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Ohio  Nevada  Missouri  Texas\n",
       "a   1.0     2.0       NaN    NaN\n",
       "b   NaN     NaN       7.0    8.0\n",
       "c   3.0     4.0       9.0   10.0\n",
       "d   NaN     NaN      11.0   12.0\n",
       "e   5.0     6.0      13.0   14.0"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also use a join instance, which is specifically designed for many DF objects with the same or similar indexes\n",
    "# but non-overlappying columns\n",
    "\n",
    "left2.join(right2, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   New York  Maine\n",
      "a       7.0    8.0\n",
      "c       9.0   10.0\n",
      "e      11.0   12.0\n",
      "f      16.0   17.0\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Nevada</th>\n",
       "      <th>Missouri</th>\n",
       "      <th>Texas</th>\n",
       "      <th>New York</th>\n",
       "      <th>Maine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Ohio  Nevada  Missouri  Texas  New York  Maine\n",
       "a   1.0     2.0       NaN    NaN       7.0    8.0\n",
       "c   3.0     4.0       9.0   10.0       9.0   10.0\n",
       "e   5.0     6.0      13.0   14.0      11.0   12.0\n",
       "b   NaN     NaN       7.0    8.0       NaN    NaN\n",
       "d   NaN     NaN      11.0   12.0       NaN    NaN\n",
       "f   NaN     NaN       NaN    NaN      16.0   17.0"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also pass in a list of DFs to the join function\n",
    "\n",
    "another = pd.DataFrame([[7., 8.], [9., 10.], [11., 12.], [16.,17.]], \n",
    "                       index = list('acef'), columns = ['New York', 'Maine'])\n",
    "\n",
    "print(another)\n",
    "\n",
    "left2.join([right2, another], how='outer') # default join type is left"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Concatenating Along an Axis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 0  1  2  3]\n",
      " [ 4  5  6  7]\n",
      " [ 8  9 10 11]]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([[ 0,  1,  2,  3],\n",
       "       [ 4,  5,  6,  7],\n",
       "       [ 8,  9, 10, 11],\n",
       "       [ 0,  1,  2,  3],\n",
       "       [ 4,  5,  6,  7],\n",
       "       [ 8,  9, 10, 11]])"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is basically just glueing on another DF on either the horizontal or vertical axis\n",
    "\n",
    "# NumPy \n",
    "\n",
    "arr = np.arange(12).reshape((3,4))\n",
    "\n",
    "print(arr)\n",
    "np.concatenate([arr,arr])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    0\n",
      "b    1\n",
      "c    2\n",
      "d    3\n",
      "e    4\n",
      "f    5\n",
      "g    6\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2\n",
       "a  0.0  NaN  NaN\n",
       "b  1.0  NaN  NaN\n",
       "c  NaN  2.0  NaN\n",
       "d  NaN  3.0  NaN\n",
       "e  NaN  4.0  NaN\n",
       "f  NaN  NaN  5.0\n",
       "g  NaN  NaN  6.0"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pandas (no index overlap)\n",
    "\n",
    "s1 = pd.Series(np.arange(2), index = list('ab'))\n",
    "s2 = pd.Series(np.arange(2,5), index = list('cde'))\n",
    "s3 = pd.Series(np.arange(5,7), index = list('fg'))\n",
    "\n",
    "print(pd.concat([s1, s2, s3]))\n",
    "\n",
    "# On the column axis (1)\n",
    "\n",
    "pd.concat([s1, s2, s3], axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "a    0\n",
      "b    1\n",
      "f    5\n",
      "g    6\n",
      "dtype: int64\n",
      "\n",
      "\n",
      "     0  1\n",
      "a  0.0  0\n",
      "b  1.0  1\n",
      "f  NaN  5\n",
      "g  NaN  6\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   0  1\n",
       "a  0  0\n",
       "b  1  1"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Same options for joining, including inner (use the join argument)\n",
    "\n",
    "s4 = pd.concat([s1, s3])\n",
    "print(s4)\n",
    "print('\\n')\n",
    "print(pd.concat([s1, s4], axis = 1))\n",
    "pd.concat([s1, s4], axis = 1, join = 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1\n",
       "a  0.0  0.0\n",
       "c  NaN  NaN\n",
       "b  1.0  1.0\n",
       "g  NaN  6.0"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also specifiy the axes to be used with the reindex method\n",
    "\n",
    "pd.concat([s1, s4], axis = 1).reindex(list('acbg'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "one    a    0\n",
      "       b    1\n",
      "two    c    2\n",
      "       d    3\n",
      "       e    4\n",
      "three  f    5\n",
      "       g    6\n",
      "dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>e</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>g</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   one  two  three\n",
       "a  0.0  NaN    NaN\n",
       "b  1.0  NaN    NaN\n",
       "c  NaN  2.0    NaN\n",
       "d  NaN  3.0    NaN\n",
       "e  NaN  4.0    NaN\n",
       "f  NaN  NaN    5.0\n",
       "g  NaN  NaN    6.0"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How to identify the concatenated pieces? Use the keys argument to create a hierarchical index\n",
    "# For the column axis (axis = 1), the keys become the column headers\n",
    "\n",
    "\n",
    "print(pd.concat([s1, s2, s3], axis = 0, keys = ['one', 'two', 'three']))\n",
    "pd.concat([s1, s2, s3], axis = 1, keys = ['one', 'two', 'three'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "one    a    0\n",
       "       b    1\n",
       "two    c    2\n",
       "       d    3\n",
       "       e    4\n",
       "three  f    5\n",
       "       g    6\n",
       "dtype: int64"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can pass in a dict instead of a list. The dict keys will be used for the keys option\n",
    "\n",
    "pd.concat({'one': s1, 'two': s2, 'three': s3})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   one  four\n",
      "a    0     1\n",
      "b    2     3\n",
      "c    4     5\n",
      "\n",
      "\n",
      "   three  two\n",
      "a      5    6\n",
      "c      7    8\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>upper</th>\n",
       "      <th colspan=\"2\" halign=\"left\">level1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">level2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lower</th>\n",
       "      <th>one</th>\n",
       "      <th>four</th>\n",
       "      <th>three</th>\n",
       "      <th>two</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "upper level1      level2     \n",
       "lower    one four  three  two\n",
       "a          0    1    5.0  6.0\n",
       "b          2    3    NaN  NaN\n",
       "c          4    5    7.0  8.0"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame(np.arange(6).reshape((3,2)), index = list('abc'), columns = ['one', 'four'])\n",
    "df2 = pd.DataFrame(5 + np.arange(4).reshape((2,2)), index = list('ac'), columns = ['three', 'two'])\n",
    "\n",
    "print(df1)\n",
    "print('\\n')\n",
    "print(df2)\n",
    "\n",
    "pd.concat({'level1': df1, 'level2': df2}, axis=1, names = ['upper', 'lower']) # Can name axis levels with names arg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          b         d         a\n",
      "0  1.581700 -0.528735  0.457002\n",
      "1  0.929969 -1.569271 -1.022487\n",
      "\n",
      "\n",
      "          a         b         c         d\n",
      "0 -0.402827  0.220487 -0.193401  0.669158\n",
      "1 -1.648985 -2.252797 -1.166832  0.353607\n",
      "2  0.702110 -0.274569 -0.139142  0.107657\n",
      "\n",
      "\n",
      "          b         d         a         c\n",
      "0  1.581700 -0.528735  0.457002       NaN\n",
      "1  0.929969 -1.569271 -1.022487       NaN\n",
      "0  0.220487  0.669158 -0.402827 -0.193401\n",
      "1 -2.252797  0.353607 -1.648985 -1.166832\n",
      "2 -0.274569  0.107657  0.702110 -0.139142\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "      <th>d</th>\n",
       "      <th>a</th>\n",
       "      <th>c</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.581700</td>\n",
       "      <td>-0.528735</td>\n",
       "      <td>0.457002</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.929969</td>\n",
       "      <td>-1.569271</td>\n",
       "      <td>-1.022487</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.220487</td>\n",
       "      <td>0.669158</td>\n",
       "      <td>-0.402827</td>\n",
       "      <td>-0.193401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-2.252797</td>\n",
       "      <td>0.353607</td>\n",
       "      <td>-1.648985</td>\n",
       "      <td>-1.166832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.274569</td>\n",
       "      <td>0.107657</td>\n",
       "      <td>0.702110</td>\n",
       "      <td>-0.139142</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          b         d         a         c\n",
       "0  1.581700 -0.528735  0.457002       NaN\n",
       "1  0.929969 -1.569271 -1.022487       NaN\n",
       "2  0.220487  0.669158 -0.402827 -0.193401\n",
       "3 -2.252797  0.353607 -1.648985 -1.166832\n",
       "4 -0.274569  0.107657  0.702110 -0.139142"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping index for when it does not contain relevant data\n",
    "\n",
    "d1 = pd.DataFrame(np.random.randn(2,3), columns = list('bda'))\n",
    "d2 = pd.DataFrame(np.random.randn(3,4), columns = list('abcd'))\n",
    "\n",
    "print(d1)\n",
    "print('\\n')\n",
    "print(d2)\n",
    "print('\\n')\n",
    "print(pd.concat([d1, d2]))\n",
    "\n",
    "\n",
    "pd.concat([d1, d2], ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reshaping and Pivoting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reshaping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          one  two  three\n",
      "state                    \n",
      "Ohio        0    1      2\n",
      "Colorado    3    4      5\n",
      "\n",
      "\n",
      "state          \n",
      "Ohio      one      0\n",
      "          two      1\n",
      "          three    2\n",
      "Colorado  one      3\n",
      "          two      4\n",
      "          three    5\n",
      "dtype: int64\n",
      "\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          one  two  three\n",
       "state                    \n",
       "Ohio        0    1      2\n",
       "Colorado    3    4      5"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame(np.arange(6).reshape((2,3)), index = pd.Index(['Ohio', 'Colorado'], name = 'state'), \n",
    "                   columns = pd.Index(['one', 'two', 'three']))\n",
    "print(data)\n",
    "print('\\n')\n",
    "\n",
    "# Using stack method pivots collumns into rows, producing a Series\n",
    "\n",
    "print(data.stack())\n",
    "print('\\n')\n",
    "\n",
    "# From a hierarchically indexed Series, can rearrange data back into a DataFrame with unstack()\n",
    "\n",
    "data.stack().unstack()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "state          \n",
       "Ohio      one      0\n",
       "          two      1\n",
       "          three    2\n",
       "Colorado  one      3\n",
       "          two      4\n",
       "          three    5\n",
       "dtype: int64"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# By default, the innermost level is unstacked/stacked. But can pass a different level number or name\n",
    "\n",
    "data.stack().unstack('state') # or level = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "side            left  right\n",
      "state                      \n",
      "Ohio     one       0      5\n",
      "         two       1      6\n",
      "         three     2      7\n",
      "Colorado one       3      8\n",
      "         two       4      9\n",
      "         three     5     10\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>side</th>\n",
       "      <th colspan=\"2\" halign=\"left\">left</th>\n",
       "      <th colspan=\"2\" halign=\"left\">right</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>one</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>two</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>three</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "side  left          right         \n",
       "state Ohio Colorado  Ohio Colorado\n",
       "one      0        3     5        8\n",
       "two      1        4     6        9\n",
       "three    2        5     7       10"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# When unstacking in a DataFrame, the level unstacked becomes the lowest level in the result\n",
    "result = data.stack()\n",
    "df = pd.DataFrame({'left': result, 'right': result + 5}, columns = pd.Index(['left', 'right'], name = 'side'))\n",
    "print(df)\n",
    "df.unstack('state')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "side            left  right\n",
      "state                      \n",
      "Ohio     one       0      5\n",
      "         two       1      6\n",
      "         three     2      7\n",
      "Colorado one       3      8\n",
      "         two       4      9\n",
      "         three     5     10\n",
      "\n",
      "\n",
      "side  left          right         \n",
      "state Ohio Colorado  Ohio Colorado\n",
      "one      0        3     5        8\n",
      "two      1        4     6        9\n",
      "three    2        5     7       10\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>Colorado</th>\n",
       "      <th>Ohio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>side</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">one</th>\n",
       "      <th>left</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">two</th>\n",
       "      <th>left</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">three</th>\n",
       "      <th>left</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>right</th>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state        Colorado  Ohio\n",
       "      side                 \n",
       "one   left          3     0\n",
       "      right         8     5\n",
       "two   left          4     1\n",
       "      right         9     6\n",
       "three left          5     2\n",
       "      right        10     7"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# And when calling stack, we can indicate the name of the axis to stack\n",
    "\n",
    "print(df)\n",
    "print('\\n')\n",
    "print(df.unstack('state'))\n",
    "df.unstack('state').stack('side')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pivoting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       year  quarter    realgdp  realcons   realinv  realgovt  realdpi  \\\n",
      "0    1959.0      1.0   2710.349    1707.4   286.898   470.045   1886.9   \n",
      "1    1959.0      2.0   2778.801    1733.7   310.859   481.301   1919.7   \n",
      "2    1959.0      3.0   2775.488    1751.8   289.226   491.260   1916.4   \n",
      "3    1959.0      4.0   2785.204    1753.7   299.356   484.052   1931.3   \n",
      "4    1960.0      1.0   2847.699    1770.5   331.722   462.199   1955.5   \n",
      "..      ...      ...        ...       ...       ...       ...      ...   \n",
      "198  2008.0      3.0  13324.600    9267.7  1990.693   991.551   9838.3   \n",
      "199  2008.0      4.0  13141.920    9195.3  1857.661  1007.273   9920.4   \n",
      "200  2009.0      1.0  12925.410    9209.2  1558.494   996.287   9926.4   \n",
      "201  2009.0      2.0  12901.504    9189.0  1456.678  1023.528  10077.5   \n",
      "202  2009.0      3.0  12990.341    9256.0  1486.398  1044.088  10040.6   \n",
      "\n",
      "         cpi      m1  tbilrate  unemp      pop  infl  realint  \n",
      "0     28.980   139.7      2.82    5.8  177.146  0.00     0.00  \n",
      "1     29.150   141.7      3.08    5.1  177.830  2.34     0.74  \n",
      "2     29.350   140.5      3.82    5.3  178.657  2.74     1.09  \n",
      "3     29.370   140.0      4.33    5.6  179.386  0.27     4.06  \n",
      "4     29.540   139.6      3.50    5.2  180.007  2.31     1.19  \n",
      "..       ...     ...       ...    ...      ...   ...      ...  \n",
      "198  216.889  1474.7      1.17    6.0  305.270 -3.16     4.33  \n",
      "199  212.174  1576.5      0.12    6.9  305.952 -8.79     8.91  \n",
      "200  212.671  1592.8      0.22    8.1  306.547  0.94    -0.71  \n",
      "201  214.469  1653.6      0.18    9.2  307.226  3.37    -3.19  \n",
      "202  216.385  1673.9      0.12    9.6  308.013  3.56    -3.44  \n",
      "\n",
      "[203 rows x 14 columns]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>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>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>13324.600</td>\n",
       "      <td>-3.16</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>13141.920</td>\n",
       "      <td>-8.79</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>12925.410</td>\n",
       "      <td>0.94</td>\n",
       "      <td>8.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>12901.504</td>\n",
       "      <td>3.37</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>12990.341</td>\n",
       "      <td>3.56</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>203 rows × 3 columns</p>\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",
       "..          ...   ...    ...\n",
       "198   13324.600 -3.16    6.0\n",
       "199   13141.920 -8.79    6.9\n",
       "200   12925.410  0.94    8.1\n",
       "201   12901.504  3.37    9.2\n",
       "202   12990.341  3.56    9.6\n",
       "\n",
       "[203 rows x 3 columns]"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select three columns to pivot into a new df with rows, columns, and values\n",
    "\n",
    "data = pd.read_csv('./pydata-book/examples/macrodata.csv')\n",
    "print(data)\n",
    "\n",
    "periods = pd.PeriodIndex(year = data.year, quarter = data.quarter, name = 'date')\n",
    "columns = pd.Index(['realgdp', 'infl', 'unemp'], name = 'item')\n",
    "data = data.reindex(columns = columns)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.index = periods.to_timestamp('D', 'end')\n",
    "ldata = data.stack().reset_index().rename(columns = {0: 'value'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "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-03-31 23:59:59.999999999</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2710.349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1959-03-31 23:59:59.999999999</td>\n",
       "      <td>infl</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1959-03-31 23:59:59.999999999</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1959-06-30 23:59:59.999999999</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2778.801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1959-06-30 23:59:59.999999999</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1959-06-30 23:59:59.999999999</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1959-09-30 23:59:59.999999999</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2775.488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1959-09-30 23:59:59.999999999</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1959-09-30 23:59:59.999999999</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1959-12-31 23:59:59.999999999</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-03-31 23:59:59.999999999  realgdp  2710.349\n",
       "1 1959-03-31 23:59:59.999999999     infl     0.000\n",
       "2 1959-03-31 23:59:59.999999999    unemp     5.800\n",
       "3 1959-06-30 23:59:59.999999999  realgdp  2778.801\n",
       "4 1959-06-30 23:59:59.999999999     infl     2.340\n",
       "5 1959-06-30 23:59:59.999999999    unemp     5.100\n",
       "6 1959-09-30 23:59:59.999999999  realgdp  2775.488\n",
       "7 1959-09-30 23:59:59.999999999     infl     2.740\n",
       "8 1959-09-30 23:59:59.999999999    unemp     5.300\n",
       "9 1959-12-31 23:59:59.999999999  realgdp  2785.204"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Long format. Two keys: date and item\n",
    "\n",
    "ldata[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "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-03-31 23:59:59.999999999</th>\n",
       "      <td>0.00</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-06-30 23:59:59.999999999</th>\n",
       "      <td>2.34</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>5.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-09-30 23:59:59.999999999</th>\n",
       "      <td>2.74</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>5.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-12-31 23:59:59.999999999</th>\n",
       "      <td>0.27</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>5.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-03-31 23:59:59.999999999</th>\n",
       "      <td>2.31</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-09-30 23:59:59.999999999</th>\n",
       "      <td>-3.16</td>\n",
       "      <td>13324.600</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-12-31 23:59:59.999999999</th>\n",
       "      <td>-8.79</td>\n",
       "      <td>13141.920</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-03-31 23:59:59.999999999</th>\n",
       "      <td>0.94</td>\n",
       "      <td>12925.410</td>\n",
       "      <td>8.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-30 23:59:59.999999999</th>\n",
       "      <td>3.37</td>\n",
       "      <td>12901.504</td>\n",
       "      <td>9.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-09-30 23:59:59.999999999</th>\n",
       "      <td>3.56</td>\n",
       "      <td>12990.341</td>\n",
       "      <td>9.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>203 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "item                           infl    realgdp  unemp\n",
       "date                                                 \n",
       "1959-03-31 23:59:59.999999999  0.00   2710.349    5.8\n",
       "1959-06-30 23:59:59.999999999  2.34   2778.801    5.1\n",
       "1959-09-30 23:59:59.999999999  2.74   2775.488    5.3\n",
       "1959-12-31 23:59:59.999999999  0.27   2785.204    5.6\n",
       "1960-03-31 23:59:59.999999999  2.31   2847.699    5.2\n",
       "...                             ...        ...    ...\n",
       "2008-09-30 23:59:59.999999999 -3.16  13324.600    6.0\n",
       "2008-12-31 23:59:59.999999999 -8.79  13141.920    6.9\n",
       "2009-03-31 23:59:59.999999999  0.94  12925.410    8.1\n",
       "2009-06-30 23:59:59.999999999  3.37  12901.504    9.2\n",
       "2009-09-30 23:59:59.999999999  3.56  12990.341    9.6\n",
       "\n",
       "[203 rows x 3 columns]"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Easier to work with if we pivot\n",
    "\n",
    "ldata.pivot('date', 'item', 'value')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "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",
       "      <th>value2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1959-03-31 23:59:59.999999999</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>1.457823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1959-03-31 23:59:59.999999999</td>\n",
       "      <td>infl</td>\n",
       "      <td>0.000</td>\n",
       "      <td>-0.176757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1959-03-31 23:59:59.999999999</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.800</td>\n",
       "      <td>-0.254240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1959-06-30 23:59:59.999999999</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>-1.263437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1959-06-30 23:59:59.999999999</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.340</td>\n",
       "      <td>0.452263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1959-06-30 23:59:59.999999999</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.100</td>\n",
       "      <td>-0.840117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1959-09-30 23:59:59.999999999</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>-0.502678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1959-09-30 23:59:59.999999999</td>\n",
       "      <td>infl</td>\n",
       "      <td>2.740</td>\n",
       "      <td>0.513393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1959-09-30 23:59:59.999999999</td>\n",
       "      <td>unemp</td>\n",
       "      <td>5.300</td>\n",
       "      <td>1.641653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1959-12-31 23:59:59.999999999</td>\n",
       "      <td>realgdp</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>0.580790</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           date     item     value    value2\n",
       "0 1959-03-31 23:59:59.999999999  realgdp  2710.349  1.457823\n",
       "1 1959-03-31 23:59:59.999999999     infl     0.000 -0.176757\n",
       "2 1959-03-31 23:59:59.999999999    unemp     5.800 -0.254240\n",
       "3 1959-06-30 23:59:59.999999999  realgdp  2778.801 -1.263437\n",
       "4 1959-06-30 23:59:59.999999999     infl     2.340  0.452263\n",
       "5 1959-06-30 23:59:59.999999999    unemp     5.100 -0.840117\n",
       "6 1959-09-30 23:59:59.999999999  realgdp  2775.488 -0.502678\n",
       "7 1959-09-30 23:59:59.999999999     infl     2.740  0.513393\n",
       "8 1959-09-30 23:59:59.999999999    unemp     5.300  1.641653\n",
       "9 1959-12-31 23:59:59.999999999  realgdp  2785.204  0.580790"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also add a second value column \n",
    "\n",
    "ldata['value2'] = np.random.randn(len(ldata))\n",
    "ldata[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">value</th>\n",
       "      <th colspan=\"3\" halign=\"left\">value2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-03-31 23:59:59.999999999</th>\n",
       "      <td>0.00</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>5.8</td>\n",
       "      <td>-0.176757</td>\n",
       "      <td>1.457823</td>\n",
       "      <td>-0.254240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-06-30 23:59:59.999999999</th>\n",
       "      <td>2.34</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>5.1</td>\n",
       "      <td>0.452263</td>\n",
       "      <td>-1.263437</td>\n",
       "      <td>-0.840117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-09-30 23:59:59.999999999</th>\n",
       "      <td>2.74</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>5.3</td>\n",
       "      <td>0.513393</td>\n",
       "      <td>-0.502678</td>\n",
       "      <td>1.641653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-12-31 23:59:59.999999999</th>\n",
       "      <td>0.27</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-1.707340</td>\n",
       "      <td>0.580790</td>\n",
       "      <td>-0.178355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-03-31 23:59:59.999999999</th>\n",
       "      <td>2.31</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1.286312</td>\n",
       "      <td>-0.828460</td>\n",
       "      <td>-0.406452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-09-30 23:59:59.999999999</th>\n",
       "      <td>-3.16</td>\n",
       "      <td>13324.600</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-0.516378</td>\n",
       "      <td>-1.122134</td>\n",
       "      <td>0.718409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008-12-31 23:59:59.999999999</th>\n",
       "      <td>-8.79</td>\n",
       "      <td>13141.920</td>\n",
       "      <td>6.9</td>\n",
       "      <td>2.527939</td>\n",
       "      <td>-1.929776</td>\n",
       "      <td>0.731370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-03-31 23:59:59.999999999</th>\n",
       "      <td>0.94</td>\n",
       "      <td>12925.410</td>\n",
       "      <td>8.1</td>\n",
       "      <td>-1.801179</td>\n",
       "      <td>0.744236</td>\n",
       "      <td>0.399881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-06-30 23:59:59.999999999</th>\n",
       "      <td>3.37</td>\n",
       "      <td>12901.504</td>\n",
       "      <td>9.2</td>\n",
       "      <td>-1.781237</td>\n",
       "      <td>-0.377000</td>\n",
       "      <td>-0.272202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009-09-30 23:59:59.999999999</th>\n",
       "      <td>3.56</td>\n",
       "      <td>12990.341</td>\n",
       "      <td>9.6</td>\n",
       "      <td>0.402790</td>\n",
       "      <td>-1.020232</td>\n",
       "      <td>1.554831</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>203 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              value                     value2            \\\n",
       "item                           infl    realgdp unemp      infl   realgdp   \n",
       "date                                                                       \n",
       "1959-03-31 23:59:59.999999999  0.00   2710.349   5.8 -0.176757  1.457823   \n",
       "1959-06-30 23:59:59.999999999  2.34   2778.801   5.1  0.452263 -1.263437   \n",
       "1959-09-30 23:59:59.999999999  2.74   2775.488   5.3  0.513393 -0.502678   \n",
       "1959-12-31 23:59:59.999999999  0.27   2785.204   5.6 -1.707340  0.580790   \n",
       "1960-03-31 23:59:59.999999999  2.31   2847.699   5.2  1.286312 -0.828460   \n",
       "...                             ...        ...   ...       ...       ...   \n",
       "2008-09-30 23:59:59.999999999 -3.16  13324.600   6.0 -0.516378 -1.122134   \n",
       "2008-12-31 23:59:59.999999999 -8.79  13141.920   6.9  2.527939 -1.929776   \n",
       "2009-03-31 23:59:59.999999999  0.94  12925.410   8.1 -1.801179  0.744236   \n",
       "2009-06-30 23:59:59.999999999  3.37  12901.504   9.2 -1.781237 -0.377000   \n",
       "2009-09-30 23:59:59.999999999  3.56  12990.341   9.6  0.402790 -1.020232   \n",
       "\n",
       "                                         \n",
       "item                              unemp  \n",
       "date                                     \n",
       "1959-03-31 23:59:59.999999999 -0.254240  \n",
       "1959-06-30 23:59:59.999999999 -0.840117  \n",
       "1959-09-30 23:59:59.999999999  1.641653  \n",
       "1959-12-31 23:59:59.999999999 -0.178355  \n",
       "1960-03-31 23:59:59.999999999 -0.406452  \n",
       "...                                 ...  \n",
       "2008-09-30 23:59:59.999999999  0.718409  \n",
       "2008-12-31 23:59:59.999999999  0.731370  \n",
       "2009-03-31 23:59:59.999999999  0.399881  \n",
       "2009-06-30 23:59:59.999999999 -0.272202  \n",
       "2009-09-30 23:59:59.999999999  1.554831  \n",
       "\n",
       "[203 rows x 6 columns]"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# By omitting the last argument, we obtain a DataFrame with hierarchical columns\n",
    "\n",
    "ldata.pivot('date', 'item')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">value</th>\n",
       "      <th colspan=\"3\" halign=\"left\">value2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th>infl</th>\n",
       "      <th>realgdp</th>\n",
       "      <th>unemp</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1959-03-31 23:59:59.999999999</th>\n",
       "      <td>0.00</td>\n",
       "      <td>2710.349</td>\n",
       "      <td>5.8</td>\n",
       "      <td>-0.176757</td>\n",
       "      <td>1.457823</td>\n",
       "      <td>-0.254240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-06-30 23:59:59.999999999</th>\n",
       "      <td>2.34</td>\n",
       "      <td>2778.801</td>\n",
       "      <td>5.1</td>\n",
       "      <td>0.452263</td>\n",
       "      <td>-1.263437</td>\n",
       "      <td>-0.840117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-09-30 23:59:59.999999999</th>\n",
       "      <td>2.74</td>\n",
       "      <td>2775.488</td>\n",
       "      <td>5.3</td>\n",
       "      <td>0.513393</td>\n",
       "      <td>-0.502678</td>\n",
       "      <td>1.641653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1959-12-31 23:59:59.999999999</th>\n",
       "      <td>0.27</td>\n",
       "      <td>2785.204</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-1.707340</td>\n",
       "      <td>0.580790</td>\n",
       "      <td>-0.178355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-03-31 23:59:59.999999999</th>\n",
       "      <td>2.31</td>\n",
       "      <td>2847.699</td>\n",
       "      <td>5.2</td>\n",
       "      <td>1.286312</td>\n",
       "      <td>-0.828460</td>\n",
       "      <td>-0.406452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-06-30 23:59:59.999999999</th>\n",
       "      <td>0.14</td>\n",
       "      <td>2834.390</td>\n",
       "      <td>5.2</td>\n",
       "      <td>0.052107</td>\n",
       "      <td>0.156632</td>\n",
       "      <td>0.955813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-09-30 23:59:59.999999999</th>\n",
       "      <td>2.70</td>\n",
       "      <td>2839.022</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-0.486323</td>\n",
       "      <td>0.743192</td>\n",
       "      <td>1.920467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960-12-31 23:59:59.999999999</th>\n",
       "      <td>1.21</td>\n",
       "      <td>2802.616</td>\n",
       "      <td>6.3</td>\n",
       "      <td>-0.173304</td>\n",
       "      <td>-0.652749</td>\n",
       "      <td>-0.360410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1961-03-31 23:59:59.999999999</th>\n",
       "      <td>-0.40</td>\n",
       "      <td>2819.264</td>\n",
       "      <td>6.8</td>\n",
       "      <td>-1.298140</td>\n",
       "      <td>-0.380414</td>\n",
       "      <td>0.527919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1961-06-30 23:59:59.999999999</th>\n",
       "      <td>1.47</td>\n",
       "      <td>2872.005</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.401185</td>\n",
       "      <td>-0.093100</td>\n",
       "      <td>-0.102583</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              value                    value2            \\\n",
       "item                           infl   realgdp unemp      infl   realgdp   \n",
       "date                                                                      \n",
       "1959-03-31 23:59:59.999999999  0.00  2710.349   5.8 -0.176757  1.457823   \n",
       "1959-06-30 23:59:59.999999999  2.34  2778.801   5.1  0.452263 -1.263437   \n",
       "1959-09-30 23:59:59.999999999  2.74  2775.488   5.3  0.513393 -0.502678   \n",
       "1959-12-31 23:59:59.999999999  0.27  2785.204   5.6 -1.707340  0.580790   \n",
       "1960-03-31 23:59:59.999999999  2.31  2847.699   5.2  1.286312 -0.828460   \n",
       "1960-06-30 23:59:59.999999999  0.14  2834.390   5.2  0.052107  0.156632   \n",
       "1960-09-30 23:59:59.999999999  2.70  2839.022   5.6 -0.486323  0.743192   \n",
       "1960-12-31 23:59:59.999999999  1.21  2802.616   6.3 -0.173304 -0.652749   \n",
       "1961-03-31 23:59:59.999999999 -0.40  2819.264   6.8 -1.298140 -0.380414   \n",
       "1961-06-30 23:59:59.999999999  1.47  2872.005   7.0  0.401185 -0.093100   \n",
       "\n",
       "                                         \n",
       "item                              unemp  \n",
       "date                                     \n",
       "1959-03-31 23:59:59.999999999 -0.254240  \n",
       "1959-06-30 23:59:59.999999999 -0.840117  \n",
       "1959-09-30 23:59:59.999999999  1.641653  \n",
       "1959-12-31 23:59:59.999999999 -0.178355  \n",
       "1960-03-31 23:59:59.999999999 -0.406452  \n",
       "1960-06-30 23:59:59.999999999  0.955813  \n",
       "1960-09-30 23:59:59.999999999  1.920467  \n",
       "1960-12-31 23:59:59.999999999 -0.360410  \n",
       "1961-03-31 23:59:59.999999999  0.527919  \n",
       "1961-06-30 23:59:59.999999999 -0.102583  "
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note that pivot is equivalent to creating a hierarchical index using set_index followed by unstack\n",
    "\n",
    "unstacked = ldata.set_index(['date', 'item']).unstack('item')\n",
    "unstacked[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Melting (inverse of pivot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "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>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bz</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   key  A  B  C\n",
       "0  foo  1  4  7\n",
       "1  bar  2  5  8\n",
       "2   bz  3  6  9"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rather than transform one column into many in a new DF, melt merges multiple columns into one\n",
    "# This produces a DF that is longer than the input\n",
    "\n",
    "df = pd.DataFrame({'key': ['foo', 'bar', 'bz'],\n",
    "                   'A': np.arange(1,4), \n",
    "                  'B': np.arange(4,7), \n",
    "                  'C': np.arange(7,10)})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "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>key</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bz</td>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foo</td>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bar</td>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>bz</td>\n",
       "      <td>B</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>foo</td>\n",
       "      <td>C</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>bar</td>\n",
       "      <td>C</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>bz</td>\n",
       "      <td>C</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   key variable  value\n",
       "0  foo        A      1\n",
       "1  bar        A      2\n",
       "2   bz        A      3\n",
       "3  foo        B      4\n",
       "4  bar        B      5\n",
       "5   bz        B      6\n",
       "6  foo        C      7\n",
       "7  bar        C      8\n",
       "8   bz        C      9"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If 'key' column is group indicator, and other columns are data values, then we can use pandas.melt and select 'key'\n",
    "\n",
    "pd.melt(df, ['key'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "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>variable</th>\n",
       "      <th>key</th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bar</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bz</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>foo</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "variable  key  A  B  C\n",
       "0         bar  2  5  8\n",
       "1          bz  3  6  9\n",
       "2         foo  1  4  7"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pivoting gets us back to the original layout\n",
    "\n",
    "pd.melt(df, ['key']).pivot('key', 'variable', 'value').reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "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>key</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bar</td>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bz</td>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foo</td>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bar</td>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>bz</td>\n",
       "      <td>B</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   key variable  value\n",
       "0  foo        A      1\n",
       "1  bar        A      2\n",
       "2   bz        A      3\n",
       "3  foo        B      4\n",
       "4  bar        B      5\n",
       "5   bz        B      6"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Can also specify a subset of columns to use as value columns\n",
    "\n",
    "pd.melt(df, id_vars = ['key'], value_vars = ['A', 'B'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "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>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>B</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>C</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>C</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>C</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  variable  value\n",
       "0        A      1\n",
       "1        A      2\n",
       "2        A      3\n",
       "3        B      4\n",
       "4        B      5\n",
       "5        B      6\n",
       "6        C      7\n",
       "7        C      8\n",
       "8        C      9"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# And finally melt can be used without any group identifiers\n",
    "\n",
    "pd.melt(df, value_vars = ['A', 'B', 'C'])"
   ]
  },
  {
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}