lessons/pandas_intro/pandas-exercise.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "## Pandas Exercise"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 1: Aggregation, Group By and Sort Review"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Below is a dataframe, with values as a list-of-lists and columns as a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>xt23</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>q45</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>q45</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>xt23</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>a89</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price\n",
       "0     123    xt23     20\n",
       "1     123     q45      2\n",
       "2     123     a89     25\n",
       "3      77     q45      3\n",
       "4      77     a89     30\n",
       "5      92    xt23     24\n",
       "6      92     m33     60\n",
       "7      92     a89     28"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame([[123,'xt23',20],\n",
    "                   [123,'q45',2],\n",
    "                   [123,'a89',25],\n",
    "                   [77,'q45',3],\n",
    "                   [77,'a89',30],\n",
    "                   [92,'xt23',24],\n",
    "                   [92,'m33',60],\n",
    "                   [92,'a89',28]], columns=['userid','product','price'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: We want the maximum price anyone has paid.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "60"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.price.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: We want the maximum price per user. Hint: Use groupby.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "userid\n",
       "77     30\n",
       "92     60\n",
       "123    25\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['userid'])['price'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: What is the total amount paid by user?**\n",
    "\n",
    "Note: Pandas will smartly leave out columns for which that aggregation doesn't have meaning."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>userid</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        price\n",
       "userid       \n",
       "77         33\n",
       "92        112\n",
       "123        47"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['userid']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: Sort the dataframe by userid first, and then price, both ascending.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>q45</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>xt23</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>a89</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>q45</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>xt23</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price\n",
       "3      77     q45      3\n",
       "4      77     a89     30\n",
       "5      92    xt23     24\n",
       "7      92     a89     28\n",
       "6      92     m33     60\n",
       "1     123     q45      2\n",
       "0     123    xt23     20\n",
       "2     123     a89     25"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(['userid','price'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 2: Combining Techniques"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: We want the maximum price each user paid, and the product associated with that price.**\n",
    "\n",
    "Note for SQL users: In SQL, you groupby and then sort, but in pandas, it's easier to do it the other way around."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "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>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price\n",
       "4      77     a89     30\n",
       "6      92     m33     60\n",
       "2     123     a89     25"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df.sort_values(['userid', 'price'], ascending=[True, False])\n",
    "    .groupby('userid')\n",
    "    .head(1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 3: Exploring The Index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Let's add a new column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "      <th>website</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>xt23</td>\n",
       "      <td>20</td>\n",
       "      <td>Amazon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>q45</td>\n",
       "      <td>2</td>\n",
       "      <td>Amazon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "      <td>NewEgg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>q45</td>\n",
       "      <td>3</td>\n",
       "      <td>NewEgg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "      <td>NewEgg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>xt23</td>\n",
       "      <td>24</td>\n",
       "      <td>Amazon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "      <td>Amazon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>a89</td>\n",
       "      <td>28</td>\n",
       "      <td>Amazon</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price website\n",
       "0     123    xt23     20  Amazon\n",
       "1     123     q45      2  Amazon\n",
       "2     123     a89     25  NewEgg\n",
       "3      77     q45      3  NewEgg\n",
       "4      77     a89     30  NewEgg\n",
       "5      92    xt23     24  Amazon\n",
       "6      92     m33     60  Amazon\n",
       "7      92     a89     28  Amazon"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['website'] = ['Amazon', 'Amazon', 'NewEgg', 'NewEgg',\n",
    "                 'NewEgg', 'Amazon', 'Amazon', 'Amazon']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: What is the total amount paid by each user on each website?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>userid</th>\n",
       "      <th>website</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <th>NewEgg</th>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <th>Amazon</th>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">123</th>\n",
       "      <th>Amazon</th>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NewEgg</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                price\n",
       "userid website       \n",
       "77     NewEgg      33\n",
       "92     Amazon     112\n",
       "123    Amazon      22\n",
       "       NewEgg      25"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['userid', 'website']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Now let's do the same groupby as above, but set the as_index flag to \"False\". This will result in a flat table instead of the nested indexes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "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>userid</th>\n",
       "      <th>website</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>77</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid website  price\n",
       "0      77  NewEgg     33\n",
       "1      92  Amazon    112\n",
       "2     123  Amazon     22\n",
       "3     123  NewEgg     25"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['userid', 'website'], as_index=False).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 4: Merging"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Let's now create a second table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>userid</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>77</td>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>92</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid country\n",
       "0     123     USA\n",
       "1      77  Canada\n",
       "2      92     USA"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame([\n",
    "    [123, 'USA'], [77, 'Canada'], [92, 'USA']],\n",
    "    columns=['userid', 'country'])\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "We can combine the two tables using a merge function. What it does is, it will do a pairwise comparision of every row in table1 with every row in table2 and if the \"on\" condition matches, it will create a single row with columns from both those matched rows.\n",
    "\n",
    "Merge of two tables with 5 rows each can give as little as 0 rows and as much as 25 rows.\n",
    "\n",
    "    [1,2,3,4,5] merged with [6,7,8,9,10] will give 0 rows\n",
    "    [1,2,3,4,5] merged with [1,2,3,4,5] will give 5 rows\n",
    "    [1,1,1,1,1] merged with [1,1,1,1,1] will give 25 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "      <th>website</th>\n",
       "      <th>country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>xt23</td>\n",
       "      <td>20</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>q45</td>\n",
       "      <td>2</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>q45</td>\n",
       "      <td>3</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>xt23</td>\n",
       "      <td>24</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>a89</td>\n",
       "      <td>28</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price website country\n",
       "0     123    xt23     20  Amazon     USA\n",
       "1     123     q45      2  Amazon     USA\n",
       "2     123     a89     25  NewEgg     USA\n",
       "3      77     q45      3  NewEgg  Canada\n",
       "4      77     a89     30  NewEgg  Canada\n",
       "5      92    xt23     24  Amazon     USA\n",
       "6      92     m33     60  Amazon     USA\n",
       "7      92     a89     28  Amazon     USA"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.merge(df, df2, on='userid')\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: What is the total amount paid per country?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country\n",
       "Canada     33\n",
       "USA       159\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.groupby(['country'])['price'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: What is the average amount paid per country and website?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country  website\n",
       "Canada   NewEgg     16.5\n",
       "USA      Amazon     26.8\n",
       "         NewEgg     25.0\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.groupby(['country', 'website'])['price'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Part 5: The Final Question - A Demo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Let's add another column: purchase date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "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>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "      <th>website</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>xt23</td>\n",
       "      <td>20</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>q45</td>\n",
       "      <td>2</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>q45</td>\n",
       "      <td>3</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>xt23</td>\n",
       "      <td>24</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>a89</td>\n",
       "      <td>28</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price website        date\n",
       "0     123    xt23     20  Amazon  2018-01-12\n",
       "1     123     q45      2  Amazon  2018-01-08\n",
       "2     123     a89     25  NewEgg  2018-01-06\n",
       "3      77     q45      3  NewEgg  2018-01-03\n",
       "4      77     a89     30  NewEgg  2018-01-05\n",
       "5      92    xt23     24  Amazon  2018-01-04\n",
       "6      92     m33     60  Amazon  2018-01-07\n",
       "7      92     a89     28  Amazon  2018-01-02"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['date'] = ['2018-01-12',\n",
    "              '2018-01-08',\n",
    "              '2018-01-06',\n",
    "              '2018-01-03',\n",
    "              '2018-01-05',\n",
    "              '2018-01-04',\n",
    "              '2018-01-07',\n",
    "              '2018-01-02']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: Here is a tricky task. For each row, I want the average purchase price for that user prior to that purchase.**\n",
    "\n",
    "One option is to do some loops. But another solution is to just do a merge on itself and filter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Movie / Wine Example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "But first, quick question:\n",
    "\n",
    "Say you merge (or 'join' if you come from SQL) two dataframes with 3 rows each, how many rows would you end up with?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Could be anything between 0-9."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "Consider the following examples, where table x has users and the movies they like. And table y has users and the wines they line. And let's do a merge to come up with possible movie and wine pairings for any user. In case A, we get 0 rows, in case B, we get 3 rows and case C we get 9 rows."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merge Two Tables with No IDs in Common"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "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>userid</th>\n",
       "      <th>movies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Godfather</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Amelie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Chicago</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid     movies\n",
       "0       1  Godfather\n",
       "1       2     Amelie\n",
       "2       3    Chicago"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfx = pd.DataFrame([[1,'Godfather'],[2,'Amelie'],[3,'Chicago']],columns=['userid','movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "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>userid</th>\n",
       "      <th>wines</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>white</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>pink</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid  wines\n",
       "0       4    red\n",
       "1       5  white\n",
       "2       6   pink"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfy = pd.DataFrame([[4,'red'],[5,'white'],[6,'pink']],columns=['userid','wines'])\n",
    "dfy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "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>userid</th>\n",
       "      <th>movies</th>\n",
       "      <th>wines</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [userid, movies, wines]\n",
       "Index: []"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfm = pd.merge(dfx,dfy,on='userid')\n",
    "dfm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merge Two Tables with IDs in Common (1 Value Per ID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "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>userid</th>\n",
       "      <th>movies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Godfather</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Amelie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Chicago</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid     movies\n",
       "0       1  Godfather\n",
       "1       2     Amelie\n",
       "2       3    Chicago"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfx = pd.DataFrame([[1,'Godfather'],[2,'Amelie'],[3,'Chicago']],columns=['userid','movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "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>userid</th>\n",
       "      <th>wines</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>white</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>pink</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid  wines\n",
       "0       1    red\n",
       "1       2  white\n",
       "2       3   pink"
      ]
     },
     "execution_count": 131,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfy = pd.DataFrame([[1,'red'],[2,'white'],[3,'pink']],columns=['userid','wines'])\n",
    "dfy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "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>userid</th>\n",
       "      <th>movies</th>\n",
       "      <th>wines</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Godfather</td>\n",
       "      <td>red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Amelie</td>\n",
       "      <td>white</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>pink</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid     movies  wines\n",
       "0       1  Godfather    red\n",
       "1       2     Amelie  white\n",
       "2       3    Chicago   pink"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfm = pd.merge(dfx,dfy,on='userid')\n",
    "dfm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Merge Two Tables with IDs in Common (Multiple Values Per ID)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "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>userid</th>\n",
       "      <th>movies</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Godfather</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Amelie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Chicago</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid     movies\n",
       "0       1  Godfather\n",
       "1       1     Amelie\n",
       "2       1    Chicago"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfx = pd.DataFrame([[1,'Godfather'],[1,'Amelie'],[1,'Chicago']],columns=['userid','movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "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>userid</th>\n",
       "      <th>wines</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>white</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>pink</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid  wines\n",
       "0       1    red\n",
       "1       1  white\n",
       "2       1   pink"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfy = pd.DataFrame([[1,'red'],[1,'white'],[1,'pink']],columns=['userid','wines'])\n",
    "dfy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "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>userid</th>\n",
       "      <th>movies</th>\n",
       "      <th>wines</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Godfather</td>\n",
       "      <td>red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Godfather</td>\n",
       "      <td>white</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Godfather</td>\n",
       "      <td>pink</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Amelie</td>\n",
       "      <td>red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Amelie</td>\n",
       "      <td>white</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Amelie</td>\n",
       "      <td>pink</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>red</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>white</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>pink</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid     movies  wines\n",
       "0       1  Godfather    red\n",
       "1       1  Godfather  white\n",
       "2       1  Godfather   pink\n",
       "3       1     Amelie    red\n",
       "4       1     Amelie  white\n",
       "5       1     Amelie   pink\n",
       "6       1    Chicago    red\n",
       "7       1    Chicago  white\n",
       "8       1    Chicago   pink"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfm = pd.merge(dfx,dfy,on='userid')\n",
    "dfm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Back to the Question"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "Now let's return to the original question: For each row, I want the average purchase price for that user prior to that purchase. Let's do a merge on itself and filter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Here are the steps we're going to take:\n",
    "1. **MERGE**: Join table on itself. For each userid / date combo, show me all userid / date / price combos.\n",
    "1. **FILTER**: For each userid / date combo, keep only the userid / date / price combos that were from earlier. Filter everything else out.\n",
    "1. **AGGREGATE**: For each userid / date combo, find the average price for the remaining rows.\n",
    "1. **MERGE**: Combine these values with the original dataframe for the final result.\n",
    "1. **SORT**: Sort to make the results look pretty."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### MERGE: Join table on itself. For each userid / date combo, show me all userid / date / price combos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "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>userid</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>2018-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>2018-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid        date\n",
       "0     123  2018-01-12\n",
       "1     123  2018-01-08\n",
       "2     123  2018-01-06\n",
       "3      77  2018-01-03\n",
       "4      77  2018-01-05\n",
       "5      92  2018-01-04\n",
       "6      92  2018-01-07\n",
       "7      92  2018-01-02"
      ]
     },
     "execution_count": 136,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_date = df[['userid','date']]\n",
    "df_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>userid</th>\n",
       "      <th>price</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>25</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>30</td>\n",
       "      <td>2018-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>24</td>\n",
       "      <td>2018-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>60</td>\n",
       "      <td>2018-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>28</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid  price        date\n",
       "0     123     20  2018-01-12\n",
       "1     123      2  2018-01-08\n",
       "2     123     25  2018-01-06\n",
       "3      77      3  2018-01-03\n",
       "4      77     30  2018-01-05\n",
       "5      92     24  2018-01-04\n",
       "6      92     60  2018-01-07\n",
       "7      92     28  2018-01-02"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_all = df[['userid','price','date']]\n",
    "df_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "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>userid</th>\n",
       "      <th>date_x</th>\n",
       "      <th>price</th>\n",
       "      <th>date_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>25</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>25</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>20</td>\n",
       "      <td>2018-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>25</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>77</td>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>77</td>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>30</td>\n",
       "      <td>2018-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>77</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>77</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>30</td>\n",
       "      <td>2018-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>24</td>\n",
       "      <td>2018-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>60</td>\n",
       "      <td>2018-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>28</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>24</td>\n",
       "      <td>2018-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>60</td>\n",
       "      <td>2018-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>28</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>24</td>\n",
       "      <td>2018-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>60</td>\n",
       "      <td>2018-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>28</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    userid      date_x  price      date_y\n",
       "0      123  2018-01-12     20  2018-01-12\n",
       "1      123  2018-01-12      2  2018-01-08\n",
       "2      123  2018-01-12     25  2018-01-06\n",
       "3      123  2018-01-08     20  2018-01-12\n",
       "4      123  2018-01-08      2  2018-01-08\n",
       "5      123  2018-01-08     25  2018-01-06\n",
       "6      123  2018-01-06     20  2018-01-12\n",
       "7      123  2018-01-06      2  2018-01-08\n",
       "8      123  2018-01-06     25  2018-01-06\n",
       "9       77  2018-01-03      3  2018-01-03\n",
       "10      77  2018-01-03     30  2018-01-05\n",
       "11      77  2018-01-05      3  2018-01-03\n",
       "12      77  2018-01-05     30  2018-01-05\n",
       "13      92  2018-01-04     24  2018-01-04\n",
       "14      92  2018-01-04     60  2018-01-07\n",
       "15      92  2018-01-04     28  2018-01-02\n",
       "16      92  2018-01-07     24  2018-01-04\n",
       "17      92  2018-01-07     60  2018-01-07\n",
       "18      92  2018-01-07     28  2018-01-02\n",
       "19      92  2018-01-02     24  2018-01-04\n",
       "20      92  2018-01-02     60  2018-01-07\n",
       "21      92  2018-01-02     28  2018-01-02"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.merge(df_date, df_all, on='userid')\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### FILTER: For each userid / date combo, keep only the userid / date / price combos that were from earlier. Filter everything else out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userid</th>\n",
       "      <th>date_x</th>\n",
       "      <th>price</th>\n",
       "      <th>date_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>25</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>123</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>25</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>77</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>3</td>\n",
       "      <td>2018-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>28</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>24</td>\n",
       "      <td>2018-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>92</td>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>28</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    userid      date_x  price      date_y\n",
       "1      123  2018-01-12      2  2018-01-08\n",
       "2      123  2018-01-12     25  2018-01-06\n",
       "5      123  2018-01-08     25  2018-01-06\n",
       "11      77  2018-01-05      3  2018-01-03\n",
       "15      92  2018-01-04     28  2018-01-02\n",
       "16      92  2018-01-07     24  2018-01-04\n",
       "18      92  2018-01-07     28  2018-01-02"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = df2[df2['date_x'] > df2['date_y']]\n",
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "#### **AGGREGATE**: For each userid / date combo, find the average price for the remaining rows."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>avg_prior_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>userid</th>\n",
       "      <th>date_x</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <th>2018-01-05</th>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">92</th>\n",
       "      <th>2018-01-04</th>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-07</th>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">123</th>\n",
       "      <th>2018-01-08</th>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-12</th>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   avg_prior_price\n",
       "userid date_x                     \n",
       "77     2018-01-05              3.0\n",
       "92     2018-01-04             28.0\n",
       "       2018-01-07             26.0\n",
       "123    2018-01-08             25.0\n",
       "       2018-01-12             13.5"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4 = df3.groupby(['userid','date_x'])[['price']].mean()\n",
    "df4.rename(columns={'price': 'avg_prior_price'}, inplace=True)\n",
    "df4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **MERGE**: Combine these values with the original dataframe for the final result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "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>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "      <th>website</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>xt23</td>\n",
       "      <td>20</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>q45</td>\n",
       "      <td>2</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>q45</td>\n",
       "      <td>3</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>xt23</td>\n",
       "      <td>24</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>a89</td>\n",
       "      <td>28</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price website        date\n",
       "0     123    xt23     20  Amazon  2018-01-12\n",
       "1     123     q45      2  Amazon  2018-01-08\n",
       "2     123     a89     25  NewEgg  2018-01-06\n",
       "3      77     q45      3  NewEgg  2018-01-03\n",
       "4      77     a89     30  NewEgg  2018-01-05\n",
       "5      92    xt23     24  Amazon  2018-01-04\n",
       "6      92     m33     60  Amazon  2018-01-07\n",
       "7      92     a89     28  Amazon  2018-01-02"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([( 77, '2018-01-05'),\n",
       "            ( 92, '2018-01-04'),\n",
       "            ( 92, '2018-01-07'),\n",
       "            (123, '2018-01-08'),\n",
       "            (123, '2018-01-12')],\n",
       "           names=['userid', 'date_x'])"
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "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>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "      <th>website</th>\n",
       "      <th>date</th>\n",
       "      <th>avg_prior_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>xt23</td>\n",
       "      <td>20</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>q45</td>\n",
       "      <td>2</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>q45</td>\n",
       "      <td>3</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>xt23</td>\n",
       "      <td>24</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>a89</td>\n",
       "      <td>28</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price website        date  avg_prior_price\n",
       "0     123    xt23     20  Amazon  2018-01-12             13.5\n",
       "1     123     q45      2  Amazon  2018-01-08             25.0\n",
       "2     123     a89     25  NewEgg  2018-01-06              NaN\n",
       "3      77     q45      3  NewEgg  2018-01-03              NaN\n",
       "4      77     a89     30  NewEgg  2018-01-05              3.0\n",
       "5      92    xt23     24  Amazon  2018-01-04             28.0\n",
       "6      92     m33     60  Amazon  2018-01-07             26.0\n",
       "7      92     a89     28  Amazon  2018-01-02              NaN"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final = df.merge(df4,\n",
    "                 left_on=['userid','date'],\n",
    "                 right_on=['userid','date_x'],\n",
    "                 right_index=True,\n",
    "                 how='left')\n",
    "final"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### **SORT**: Sort to make the results look pretty."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "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>userid</th>\n",
       "      <th>product</th>\n",
       "      <th>price</th>\n",
       "      <th>website</th>\n",
       "      <th>date</th>\n",
       "      <th>avg_prior_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>77</td>\n",
       "      <td>q45</td>\n",
       "      <td>3</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>77</td>\n",
       "      <td>a89</td>\n",
       "      <td>30</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-05</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>92</td>\n",
       "      <td>a89</td>\n",
       "      <td>28</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92</td>\n",
       "      <td>xt23</td>\n",
       "      <td>24</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92</td>\n",
       "      <td>m33</td>\n",
       "      <td>60</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-07</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>123</td>\n",
       "      <td>a89</td>\n",
       "      <td>25</td>\n",
       "      <td>NewEgg</td>\n",
       "      <td>2018-01-06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>q45</td>\n",
       "      <td>2</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>123</td>\n",
       "      <td>xt23</td>\n",
       "      <td>20</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid product  price website        date  avg_prior_price\n",
       "3      77     q45      3  NewEgg  2018-01-03              NaN\n",
       "4      77     a89     30  NewEgg  2018-01-05              3.0\n",
       "7      92     a89     28  Amazon  2018-01-02              NaN\n",
       "5      92    xt23     24  Amazon  2018-01-04             28.0\n",
       "6      92     m33     60  Amazon  2018-01-07             26.0\n",
       "2     123     a89     25  NewEgg  2018-01-06              NaN\n",
       "1     123     q45      2  Amazon  2018-01-08             25.0\n",
       "0     123    xt23     20  Amazon  2018-01-12             13.5"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final.sort_values(by=['userid','date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Wow that was tricky!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  },
  "toc": {
   "nav_menu": {
    "height": "12px",
    "width": "252px"
   },
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}