Lab/Lecture_05/L05_Hashing_Applications_no_sols.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hashing: Applications\n",
    "\n",
    "You already know several applications of Python's set and dictionary (thus, hashing) in your programming life. \n",
    "For example, computing frequencies of distinct strings in text or numbers in a list.\n",
    "\n",
    "Today we will see a very important application in managing data bases: \n",
    "- Data aggregation by grouping rows by values in some colum (aka **GroupBy**)\n",
    "- Merging different datasets (aka **Join**) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Data Aggregation in Pandas: GroupBy\n",
    "\n",
    "Groupby is a pretty easy concept: *we create a grouping of categories and apply an aggregation function to element of each category*.\n",
    "\n",
    "It's a simple concept but it's an extremely valuable technique that's widely used in data science. The value of groupby really comes from its ability to **aggregate data efficiently**, both in performance and the amount code it takes. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first create a toy DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>animal</th>\n",
       "      <th>age</th>\n",
       "      <th>weight</th>\n",
       "      <th>length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cat</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cat</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>alligator</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dog</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>dog</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>dog</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>cat</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>cat</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>snake</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>cat</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>cat</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>cat</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>dog</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>dog</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       animal  age  weight  length\n",
       "0         cat    1      13       6\n",
       "1         cat    4      14       5\n",
       "2   alligator    1       5       3\n",
       "3         dog    3      13       4\n",
       "4         dog    3       8       6\n",
       "5         dog    2      13       2\n",
       "6         cat    1      11       2\n",
       "7         cat    1       8       7\n",
       "8       snake    2      12       2\n",
       "9         cat    3      14       6\n",
       "10        cat    1       9       6\n",
       "11        cat    2       5       5\n",
       "12        dog    1       7       1\n",
       "13        dog    4      11       8"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import random\n",
    "import pandas as pd\n",
    "\n",
    "random.seed(42)\n",
    "    \n",
    "# Random pets column\n",
    "pet_list = [\"cat\", \"dog\", \"alligator\", \"snake\"]\n",
    "pet = [random.choice(pet_list) for i in range(1,15)]\n",
    "\n",
    "# Random weight of animal column\n",
    "weight = [random.choice(range(5,15)) for i in range(1,15)]\n",
    "\n",
    "# Random length of animals column\n",
    "length = [random.choice(range(1,10)) for i in range(1,15)]\n",
    "\n",
    "# random age of the animals column\n",
    "age = [random.choice(range(1,5)) for i in range(1,15)]\n",
    "\n",
    "# Put everyhting into a dataframe\n",
    "df = pd.DataFrame()\n",
    "df[\"animal\"] = pet\n",
    "df[\"age\"] = age\n",
    "df[\"weight\"] = weight\n",
    "df[\"length\"] = length\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Groupby a variable\n",
    "\n",
    "Groupby essentially splits the data into different groups *depending on a variable* (or more variables) of your choice. \n",
    "\n",
    "For example, the expression `df.groupby('animal')` splits the DataFrame by 'animal' category.\n",
    "\n",
    "The `groupby()` function returns a *GroupBy* object, but essentially describes how the rows of the original dataset has been split. \n",
    "\n",
    "In the GroupBy object `groups` variable is a dictionary whose keys are the unique groups and corresponding values being the axis labels belonging to each group."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.groupby.generic.DataFrameGroupBy'>\n",
      "\n",
      "{'alligator': Int64Index([2], dtype='int64'), 'cat': Int64Index([0, 1, 6, 7, 9, 10, 11], dtype='int64'), 'dog': Int64Index([3, 4, 5, 12, 13], dtype='int64'), 'snake': Int64Index([8], dtype='int64')}\n",
      "\n",
      "dict_keys(['alligator', 'cat', 'dog', 'snake'])\n"
     ]
    }
   ],
   "source": [
    "animal_groups = df.groupby('animal') # Group by animal category\n",
    "\n",
    "print( type(animal_groups), end='\\n\\n')\n",
    "\n",
    "print( animal_groups.groups, end='\\n\\n')\n",
    "\n",
    "print( animal_groups.groups.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have a grouping, we can apply an aggregation function to the groupby object to get the \n",
    "function applied to the elements of each group. \n",
    "\n",
    "For example,\n",
    "\n",
    "```python\n",
    "animal_groups = df.groupby(\"animal\")\n",
    "\n",
    "gdf = animal_groups.max()\n",
    "\n",
    "```\n",
    "\n",
    "computes the maximum value in each column of each group. \n",
    "\n",
    "There are several possible aggregation functions. For example, \n",
    "\n",
    "```text\n",
    "mean()\n",
    "std()\n",
    "median()\t\n",
    "min()\n",
    "max()\n",
    "sum()\n",
    "count()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>weight</th>\n",
       "      <th>length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>animal</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>alligator</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat</th>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog</th>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>snake</th>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           age  weight  length\n",
       "animal                        \n",
       "alligator    1       5       3\n",
       "cat          4      14       7\n",
       "dog          4      13       8\n",
       "snake        2      12       2"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "animal_groups = df.groupby('animal')\n",
    "\n",
    "gdf = animal_groups.max()\n",
    "\n",
    "print( type(gdf) ) # gdf is a DataFrame\n",
    "\n",
    "gdf "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example\n",
    "What's the average weight of each kind of animal?\n",
    "\n",
    "To find the average weight of each category of animal, we group the animals by 'animal' and, then, we apply the *mean* function. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Avg weight: animal\n",
      "alligator     5.000000\n",
      "cat          10.571429\n",
      "dog          10.400000\n",
      "snake        12.000000\n",
      "Name: weight, dtype: float64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>weight</th>\n",
       "      <th>length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>animal</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>alligator</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cat</th>\n",
       "      <td>1.857143</td>\n",
       "      <td>10.571429</td>\n",
       "      <td>5.285714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dog</th>\n",
       "      <td>2.600000</td>\n",
       "      <td>10.400000</td>\n",
       "      <td>4.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>snake</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                age     weight    length\n",
       "animal                                  \n",
       "alligator  1.000000   5.000000  3.000000\n",
       "cat        1.857143  10.571429  5.285714\n",
       "dog        2.600000  10.400000  4.200000\n",
       "snake      2.000000  12.000000  2.000000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Avg weight:\", animal_groups.mean()['weight'])\n",
    "\n",
    "animal_groups.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example\n",
    "What's the overall length of snakes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sum snakes' length: 2\n"
     ]
    }
   ],
   "source": [
    "s = animal_groups.sum().loc['snake', 'length'] # Access snake row, length col\n",
    "\n",
    "# Alternative: s = animal_groups['length'].sum().loc['snake'] # may be faster\n",
    "\n",
    "print(\"Sum snakes' length:\", s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example\n",
    "What's the age of the oldest cat?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Age older cat: 4\n"
     ]
    }
   ],
   "source": [
    "s = animal_groups['age'].max().loc['cat']\n",
    "\n",
    "print(\"Age older cat:\", s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GroupBy more variables\n",
    "\n",
    "It is also possible to group by more than one variable, allowing more complex queries.\n",
    "\n",
    "For example,\n",
    "\n",
    "```python\n",
    "animalage_groups = df.groupby( ['animal', 'age'] )\n",
    "```\n",
    "\n",
    "groups first by 'animal' and then, by 'age' within each group.\n",
    "\n",
    "Note the list of labels in calling `groupby`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{('alligator', 1): Int64Index([2], dtype='int64'),\n",
       " ('cat', 1): Int64Index([0, 6, 7, 10], dtype='int64'),\n",
       " ('cat', 2): Int64Index([11], dtype='int64'),\n",
       " ('cat', 3): Int64Index([9], dtype='int64'),\n",
       " ('cat', 4): Int64Index([1], dtype='int64'),\n",
       " ('dog', 1): Int64Index([12], dtype='int64'),\n",
       " ('dog', 2): Int64Index([5], dtype='int64'),\n",
       " ('dog', 3): Int64Index([3, 4], dtype='int64'),\n",
       " ('dog', 4): Int64Index([13], dtype='int64'),\n",
       " ('snake', 2): Int64Index([8], dtype='int64')}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "animalage_groups = df.groupby( ['animal', 'age'] )\n",
    "\n",
    "animalage_groups.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Example\n",
    "What's the maximum weight of a 3-years old dog?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "animal     age\n",
      "alligator  1       5\n",
      "cat        1      13\n",
      "           2       5\n",
      "           3      14\n",
      "           4      14\n",
      "dog        1       7\n",
      "           2      13\n",
      "           3      13\n",
      "           4      11\n",
      "snake      2      12\n",
      "Name: weight, dtype: int64\n",
      "\n",
      "age\n",
      "1     7\n",
      "2    13\n",
      "3    13\n",
      "4    11\n",
      "Name: weight, dtype: int64\n",
      "\n",
      "3yo dog weight: 13\n"
     ]
    }
   ],
   "source": [
    "print( animalage_groups['weight'].max() )               # operation max () \n",
    "print()\n",
    "\n",
    "print( animalage_groups['weight'].max().loc['dog'] )  # .loc['dog']\n",
    "print()\n",
    "\n",
    "print(\"3yo dog weight:\", animalage_groups['weight'].max().loc[('dog', 3)] ) # Multiindex we use a pair "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "--- \n",
    "### Exercise: Implements your own GroupBy\n",
    "\n",
    "In the rest of your life you are going to use GroupBy implemented in some library, but in this exercise we will implement our own simplified version. \n",
    "\n",
    "You are give a list of tuples, all with the same number of components. In our simplified implementation  of a pandas' DataFrame each tuple in the list is a row of the DataFrame. Each component of a tuple is a value of a column.\n",
    "\n",
    "#### Part I\n",
    "Our first goal is to implement an index to efficiently group by one of the component in the list. \n",
    "\n",
    "We'd like to implement a function ```groupBy(L, id)``` which takes the list of tuples ```L``` and the ```id``` of the component and returns a dictionary. The dictionary is an index very similar to what you implemented for a search engine. \n",
    "We have a key for each distinct value in column ```id```. The value of a certain key ```k``` is the list of indexes of all the tuple having value ```k``` in the column ```id```.\n",
    "This means that, if index ```p``` is in the list of key ```k```, then ```L[p][id] = k```.\n",
    "\n",
    "For example it we have tuples \n",
    "\n",
    "|   | \n",
    "|:-|\n",
    "(1, 5, 11)\n",
    "(0, 4, 1000)\n",
    "(1, 2, 11)\n",
    "(1, 4, 66) \n",
    "(0, 3, 99)\n",
    "\n",
    "The groupBy with id=0 will group by first column.\n",
    "\n",
    "The index is\n",
    "\n",
    "|   | \n",
    "|:--| \n",
    "0: [1, 4]\n",
    "1: [0, 2, 3]\n",
    " \n",
    "#### Part II\n",
    "We'd like to implement a function ```max_groupBy(index, L)``` which takes the index built in previous part on list ```L``` and returns a dictionary. \n",
    "We have a key for each distinct value in column ```id```. The value of a certain key ```k``` is the list. The list has a element for each column: the maximum value in that column for each tuple having value ```k``` in the column ```id```. This, of course, must be implemented by using the index.\n",
    "\n",
    "In the example before, we would obtain the dictionary\n",
    "\n",
    "|   | \n",
    "|:--| \n",
    "0: [0, 4, 1000]\n",
    "1: [1, 5, 66]\n",
    " \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Your implementation goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Test you implementation here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [(1, 5, 11), (0, 4, 1000), (1, 2, 11), (1, 4, 66), (0, 3, 99)]\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Combining and Merging Datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data contained in Pandas objects can be combined together in a number of ways:\n",
    "    \n",
    "- **merge()** connects rows in DataFrames based on one or more keys. This will be familiar to users of SQL or other relational databases, as it implements database **join** operations.\n",
    "- **concat()** concatenates or “stacks” together objects along an axis.\n",
    "- The **combine_first()** instance method enables splicing together overlapping data to fill in missing values in one object with values from another.\n",
    "\n",
    "Here we will cover only the first two ways."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrame Joins\n",
    "\n",
    "#### Many-to-One join\n",
    "Consider the following two DataFrames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1\n",
      "0   b      0\n",
      "1   b      1\n",
      "2   a      2\n",
      "3   c      3\n",
      "4   a      4\n",
      "5   a      5\n",
      "6   b      6\n",
      "  key  data2\n",
      "0   a      0\n",
      "1   b      1\n",
      "2   d      2\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], \n",
    "                    'data1': range(7)})\n",
    "\n",
    "df2 = pd.DataFrame({'key': ['a', 'b', 'd'], \n",
    "                    'data2': range(3)})\n",
    "\n",
    "print(df1)\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data in df1 has multiple rows labeled a and b, whereas df2 has only one row for each value in the key column. \n",
    "\n",
    "The operation \n",
    "\n",
    "```\n",
    "pd.merge(df1, df2, on='key')\n",
    "```\n",
    "\n",
    "will produce a merged DataFrame with key the column to join on. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data1  data2\n",
       "0   b      0      1\n",
       "1   b      1      1\n",
       "2   b      6      1\n",
       "3   a      2      0\n",
       "4   a      4      0\n",
       "5   a      5      0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on='key')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that \n",
    "- data2 of df2 is replicated everywhere key 'a' and 'b' occur in df1\n",
    "- rows with key 'c' in df1 and 'd' in df2 are not present in df\n",
    "\n",
    "The latter behavior is called **inner** join: the keys in the result are the intersection, or the common set found in both tables.\n",
    "\n",
    "Other possible approaches are: \n",
    "- **left**: keys of left DataFrame are kept\n",
    "- **right**: keys of right DataFrame are kept\n",
    "- **outer**: keys of both DataFrames are kept\n",
    "\n",
    "and can be chosen by setting parameter *how*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>b</td>\n",
       "      <td>6</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data1  data2\n",
       "0   b      0    1.0\n",
       "1   b      1    1.0\n",
       "2   a      2    0.0\n",
       "3   c      3    NaN\n",
       "4   a      4    0.0\n",
       "5   a      5    0.0\n",
       "6   b      6    1.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on='key', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data1  data2\n",
       "0   b    0.0      1\n",
       "1   b    1.0      1\n",
       "2   b    6.0      1\n",
       "3   a    2.0      0\n",
       "4   a    4.0      0\n",
       "5   a    5.0      0\n",
       "6   d    NaN      2"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on='key', how='right')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### outer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>c</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>d</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data1  data2\n",
       "0   b    0.0    1.0\n",
       "1   b    1.0    1.0\n",
       "2   b    6.0    1.0\n",
       "3   a    2.0    0.0\n",
       "4   a    4.0    0.0\n",
       "5   a    5.0    0.0\n",
       "6   c    3.0    NaN\n",
       "7   d    NaN    2.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df1, df2, on='key', how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Many-to-Many join\n",
    "Many-to-many merges happens when the same key has more than one occurrence in both DataFrames.\n",
    "\n",
    "Many-to-many joins form the Cartesian product of the rows having the same key. \n",
    "\n",
    "See the following example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  key  data1\n",
      "0   b      0\n",
      "1   b      1\n",
      "2   a      2\n",
      "3   a      3\n",
      "4   b      4\n",
      "  key  data2\n",
      "0   a      0\n",
      "1   b      1\n",
      "2   a      2\n",
      "3   b      3\n",
      "4   d      4\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key</th>\n",
       "      <th>data1</th>\n",
       "      <th>data2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>b</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>b</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>a</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>a</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>b</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key  data1  data2\n",
       "0   b      0      1\n",
       "1   b      0      3\n",
       "2   b      1      1\n",
       "3   b      1      3\n",
       "4   a      2      0\n",
       "5   a      2      2\n",
       "6   a      3      0\n",
       "7   a      3      2\n",
       "8   b      4      1\n",
       "9   b      4      3"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame({'key': ['b', 'b', 'a',  'a', 'b'], \n",
    "                    'data1': range(5)})\n",
    "                    \n",
    "df2 = pd.DataFrame({'key': ['a', 'b', 'a', 'b', 'd'],\n",
    "                    'data2': range(5)})\n",
    "\n",
    "print(df1)\n",
    "\n",
    "\n",
    "print(df2)\n",
    "\n",
    "                    \n",
    "pd.merge(df1, df2, on='key', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that, since there were three 'b' rows in df1 and two in df2, there are six 'b' rows in the result.\n",
    "\n",
    "---\n",
    "\n",
    "The merge can be performed also respect to more than one variable. To determine which key combinations will appear in the result depending on the choice of merge method, **think of the multiple keys as forming an array of tuples** to be used as a single join key."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "### How to implement join\n",
    "Join can be implemented by using three different algorithms: \n",
    "- **Nested Loops Join**: For each row in of the first dataset, check each row of the second dataset. \n",
    "- **Hash Join**: for each distinct value of the join attribute, the set of tuples in each relation which have that value. (here)[https://en.wikipedia.org/wiki/Hash_join]. This very close to the index you have built for previous exercise. \n",
    "- **Sort-merge Join**: The key idea of the sort-merge algorithm is to first sort the relations by the join attribute, so that interleaved linear scans will encounter these sets at the same time. [here](https://en.wikipedia.org/wiki/Sort-merge_join)\n",
    "\n",
    "If the datasets are already sorted, the third approach is the fastest with margin. Otherwise, the second one is usually the fastest one. Of course, performance depends on the number of distinct value of the join attribute.\n",
    "\n",
    "A good **optional** exercise is to implement and compare the three possible join algorithms."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "---\n",
    "\n",
    "# References\n",
    "\n",
    " - **Pandas Tutorial**. https://pandas.pydata.org/pandas-docs/stable/tutorials.html\n",
    " - **Python for data analysis**. Second Edition.\n",
    "   - Chapters 7, 8 and 10 \n",
    " - **Python data science handbook**. Third Edition.\n",
    "   - Chapter 3\n",
    " - https://medium.com/dunder-data/selecting-subsets-of-data-in-pandas-39e811c81a0c"
   ]
  }
 ],
 "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.8.5"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": 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": 2
}