20-problem-solution_joining_datasets_merge_join.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following is an excerpt from Chapter 5 of Watson's Data Management 6th Edition\n",
    "\n",
    "> Consider the case when items are sold. We can immediately identify two entities: SALE and ITEM. A sale can contain many items, and an item can appear in many sales. We are not saying the same item can be sold many times, but the particular type of item (e.g., a compass) can be sold many times; thus we have a many-to-many (m:m) relationship between SALE and ITEM. When we have an m:m relationship, we create a third entity to link the entities through two 1:m relationships. Usually, it is fairly easy to name this third entity. In this case, this third entity, typically known as an associative entity, is called LINE ITEM. A typical sales form lists the items purchased by a customer. Each of the lines appearing on the order form is generally known in retailing as a line item, which links an item and a sale.\n",
    "\n",
    "The following ERD shows the data schema\n",
    "\n",
    "![ERD](images/sale_erd.png)\n",
    "\n",
    "The file `data/sales_data.xlsx` is an Excel spreadsheet containing three workbooks with data about `sale`, `item`, and `lineitem`. The code below loads the data into Pandas data frames. Use the data frames to answer the following questions:\n",
    "\n",
    "- List the name, quantity, price, and value of items sold on January 16, 2021\n",
    "- List items that were sold on January 16, 2021, or are brown\n",
    "- List items that were sold on January 16, 2021, and are brow\n",
    "- Report all clothing items (type “C”) for which a sale is recorded\n",
    "- Report all clothing items that have not been sold\n",
    "- Find the items that have appeared in all sales\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "sale = pd.read_excel('data/sales_data.xlsx', sheet_name='sale')\n",
    "item = pd.read_excel('data/sales_data.xlsx', sheet_name='item') \n",
    "lineitem = pd.read_excel('data/sales_data.xlsx', sheet_name='lineitem') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>SALENO</th>\n",
       "      <th>SALEDATE</th>\n",
       "      <th>SALETEXT</th>\n",
       "      <th>LINENO</th>\n",
       "      <th>LINEQTY</th>\n",
       "      <th>LINEPRICE</th>\n",
       "      <th>ITEMNO</th>\n",
       "      <th>ITEMNAME</th>\n",
       "      <th>ITEMTYPE</th>\n",
       "      <th>ITEMCOLOR</th>\n",
       "      <th>TOTAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2021-01-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.50</td>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>4.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2021-01-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>2.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2021-01-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>2.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2021-01-15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>2.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2021-01-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SALENO   SALEDATE  SALETEXT  LINENO  LINEQTY  LINEPRICE  ITEMNO  \\\n",
       "0       1 2021-01-15       NaN       1        1       4.50       2   \n",
       "1       2 2021-01-15       NaN       4        1       2.25       2   \n",
       "2       3 2021-01-15       NaN       2        1       2.25       2   \n",
       "3       4 2021-01-15       NaN       6        1       2.25       2   \n",
       "4       5 2021-01-16       NaN       5        1       0.00       2   \n",
       "\n",
       "              ITEMNAME ITEMTYPE ITEMCOLOR  TOTAL  \n",
       "0  Pocket knife - Avon        E     Brown   4.50  \n",
       "1  Pocket knife - Avon        E     Brown   2.25  \n",
       "2  Pocket knife - Avon        E     Brown   2.25  \n",
       "3  Pocket knife - Avon        E     Brown   2.25  \n",
       "4  Pocket knife - Avon        E     Brown   0.00  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.merge( pd.merge(sale, lineitem), item)\n",
    "data['TOTAL'] = data.LINEQTY * data.LINEPRICE\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>ITEMNAME</th>\n",
       "      <th>LINEQTY</th>\n",
       "      <th>LINEPRICE</th>\n",
       "      <th>TOTAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Tent - 2 person</td>\n",
       "      <td>1</td>\n",
       "      <td>60.0</td>\n",
       "      <td>60.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Safari chair</td>\n",
       "      <td>50</td>\n",
       "      <td>36.0</td>\n",
       "      <td>1800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Hammock</td>\n",
       "      <td>50</td>\n",
       "      <td>40.5</td>\n",
       "      <td>2025.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Tent - 8 person</td>\n",
       "      <td>8</td>\n",
       "      <td>153.0</td>\n",
       "      <td>1224.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ITEMNAME  LINEQTY  LINEPRICE   TOTAL\n",
       "4   Pocket knife - Avon        1        0.0     0.0\n",
       "12      Tent - 2 person        1       60.0    60.0\n",
       "15         Safari chair       50       36.0  1800.0\n",
       "16              Hammock       50       40.5  2025.0\n",
       "17      Tent - 8 person        8      153.0  1224.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jan_16_items = data.loc[data.SALEDATE=='2021-01-16', ['ITEMNAME', 'LINEQTY', 'LINEPRICE', 'TOTAL']]\n",
    "jan_16_items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using set operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "brown_items = item.loc[item.ITEMCOLOR == 'Brown', 'ITEMNAME']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Hammock',\n",
       " 'Map case',\n",
       " 'Pocket knife - Avon',\n",
       " 'Pocket knife - Nile',\n",
       " 'Safari chair',\n",
       " 'Stetson',\n",
       " 'Tent - 2 person',\n",
       " 'Tent - 8 person'}"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jan_16_items_or_brown = set(jan_16_items.ITEMNAME) | set(brown_items)\n",
    "jan_16_items_or_brown"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Pocket knife - Avon\n",
       "1        Tent - 2 person\n",
       "2           Safari chair\n",
       "3                Hammock\n",
       "4        Tent - 8 person\n",
       "5    Pocket knife - Nile\n",
       "6               Map case\n",
       "7                Stetson\n",
       "Name: ITEMNAME, dtype: object"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(jan_16_items, brown_items, how='outer').ITEMNAME"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using set operations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Pocket knife - Avon'}"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jan_16_items_and_brown = set(jan_16_items.ITEMNAME) & set(brown_items)\n",
    "jan_16_items_and_brown"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Pocket knife - Avon\n",
       "Name: ITEMNAME, dtype: object"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(jan_16_items, brown_items, how='inner').ITEMNAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5     Hat - Polar explorer\n",
       "6              Pith helmet\n",
       "7                  Stetson\n",
       "10     Boots - snake proof\n",
       "Name: ITEMNAME, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sold_C_items = data.loc[data.ITEMTYPE=='C', 'ITEMNAME']\n",
    "sold_C_items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>ITEMNO</th>\n",
       "      <th>ITEMNAME</th>\n",
       "      <th>ITEMTYPE</th>\n",
       "      <th>ITEMCOLOR</th>\n",
       "      <th>LINENO</th>\n",
       "      <th>LINEQTY</th>\n",
       "      <th>LINEPRICE</th>\n",
       "      <th>SALENO</th>\n",
       "      <th>UNSOLD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Pocket knife - Nile</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.25</td>\n",
       "      <td>2.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.25</td>\n",
       "      <td>3.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Pocket knife - Avon</td>\n",
       "      <td>E</td>\n",
       "      <td>Brown</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.25</td>\n",
       "      <td>4.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ITEMNO             ITEMNAME ITEMTYPE ITEMCOLOR  LINENO  LINEQTY  LINEPRICE  \\\n",
       "0       1  Pocket knife - Nile        E     Brown     NaN      NaN        NaN   \n",
       "1       2  Pocket knife - Avon        E     Brown     1.0      1.0       4.50   \n",
       "2       2  Pocket knife - Avon        E     Brown     4.0      1.0       2.25   \n",
       "3       2  Pocket knife - Avon        E     Brown     2.0      1.0       2.25   \n",
       "4       2  Pocket knife - Avon        E     Brown     6.0      1.0       2.25   \n",
       "\n",
       "   SALENO  UNSOLD  \n",
       "0     NaN    True  \n",
       "1     1.0   False  \n",
       "2     2.0   False  \n",
       "3     3.0   False  \n",
       "4     4.0   False  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sold_and_unsold_items = pd.merge(item, lineitem, how='outer')\n",
    "sold_and_unsold_items['UNSOLD'] = sold_and_unsold_items.SALENO.isnull()\n",
    "sold_and_unsold_items.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "11    Hat - Polar explorer\n",
       "12     Boots - snake proof\n",
       "20             Pith helmet\n",
       "25                 Stetson\n",
       "Name: ITEMNAME, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unsold_C_items = sold_and_unsold_items.loc[sold_and_unsold_items.UNSOLD & (sold_and_unsold_items.ITEMTYPE == 'C'), 'ITEMNAME']\n",
    "unsold_C_items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Items on all sales:\n",
    "- count sales\n",
    "- count number of sales per item\n",
    "- select items with equal value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "tot_sales = len(sale)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "cube = data.set_index(['ITEMNO', 'SALENO']).LINEQTY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "sale_count = cube.count(level='ITEMNO')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "items_on_all_sales = sale_count[sale_count == tot_sales]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ITEMNO\n",
       "2    5\n",
       "Name: LINEQTY, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items_on_all_sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    Pocket knife - Avon\n",
       "Name: ITEMNAME, dtype: object"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(items_on_all_sales, item, left_index=True, right_on='ITEMNO').ITEMNAME"
   ]
  },
  {
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}