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": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "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": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": []
  },
  {
   "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": []
  },
  {
   "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": []
  },
  {
   "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": []
  },
  {
   "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'] = ['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": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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": {},
   "outputs": [],
   "source": []
  },
  {
   "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']],\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": 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": []
  },
  {
   "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": []
  },
  {
   "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'] = ['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": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfx = pd.DataFrame([[1,'Godfather'],[2,'Amelie'],[3,'Chicago']],columns=['userid','movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfy = pd.DataFrame([[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([[1,'Godfather'],[2,'Amelie'],[3,'Chicago']],columns=['userid','movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfy = pd.DataFrame([[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([[1,'Godfather'],[1,'Amelie'],[1,'Chicago']],columns=['userid','movies'])\n",
    "dfx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfy = pd.DataFrame([[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(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": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "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
}