lessons/pandas_intro/solution/pandas-solution.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": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    [[123, 'xt23', 20], [123, 'q45', 2], [123, 'a89', 25], [77, 'q45', 3],\n",
    "     [77, 'a89', 30], [92, 'xt23', 24], [92, 'm33', 60], [92, 'a89', 28]],\n",
    "    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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['price'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['price'] == 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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('userid')[['price']].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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(['userid', 'price'], ascending=[True, True])"
   ]
  },
  {
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(df.sort_values(['userid', 'price'], ascending=[True, False]).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": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df['website'] = [\n",
    "    'Amazon', 'Amazon', 'NewEgg', 'NewEgg', 'NewEgg', 'Amazon', 'Amazon',\n",
    "    'Amazon'\n",
    "]\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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "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": null,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "df2 = pd.DataFrame(\n",
    "    [[123, 'USA'], [77, 'Canada'], [92, 'USA']], 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": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "pd.merge(df, df2, on='userid')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "**Q: What is the total amount paid per country?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df, df2, on='userid').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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(pd.merge(df, df2, on='userid').groupby(['country', 'website'])[['price']]\n",
    " .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": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df['date'] = [\n",
    "    '2018-01-12', '2018-01-08', '2018-01-06', '2018-01-03', '2018-01-05',\n",
    "    '2018-01-04', '2018-01-07', '2018-01-02'\n",
    "]\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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfx = pd.DataFrame(\n",
    "    [[1, 'Godfather'], [2, 'Amelie'], [3, 'Chicago']],\n",
    "    columns=['userid', 'movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfy = pd.DataFrame(\n",
    "    [[4, 'red'], [5, 'white'], [6, 'pink']], columns=['userid', 'wines'])\n",
    "dfy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfx = pd.DataFrame(\n",
    "    [[1, 'Godfather'], [2, 'Amelie'], [3, 'Chicago']],\n",
    "    columns=['userid', 'movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfy = pd.DataFrame(\n",
    "    [[1, 'red'], [2, 'white'], [3, 'pink']], columns=['userid', 'wines'])\n",
    "dfy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfx = pd.DataFrame(\n",
    "    [[1, 'Godfather'], [1, 'Amelie'], [1, 'Chicago']],\n",
    "    columns=['userid', 'movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfy = pd.DataFrame(\n",
    "    [[1, 'red'], [1, 'white'], [1, 'pink']], columns=['userid', 'wines'])\n",
    "dfy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_date = df[['userid', 'date']]\n",
    "df_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_all = df[['userid', 'price', 'date']]\n",
    "df_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final = df.merge(\n",
    "    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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final.sort_values(by=['userid', 'date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "metis",
   "language": "python",
   "name": "metis"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}