SQL & Pandas LEFT JOIN Solution.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LEFT JOIN with Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 렉처 중간에 예제 문제가 있습니다. 풀면서 따라와주세요!\n",
    "**첨부한 쿼리는 [이곳](https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all)에서 동작합니다.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 데이터 읽기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Orders.csv')\n",
    "orderDetails = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/OrderDetails.csv')\n",
    "products = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Products.csv')\n",
    "customers = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Customers.csv')\n",
    "categories = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Categories.csv')\n",
    "suppliers = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Suppliers.csv')\n",
    "shippers = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Shippers.csv')\n",
    "employees = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Employees.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 데이터 확인하기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>CustomerID</th>\n",
       "      <th>CustomerName</th>\n",
       "      <th>ContactName</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alfreds Futterkiste</td>\n",
       "      <td>Maria Anders</td>\n",
       "      <td>Obere Str. 57</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>12209</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ana Trujillo Emparedados y helados</td>\n",
       "      <td>Ana Trujillo</td>\n",
       "      <td>Avda. de la Constitución 2222</td>\n",
       "      <td>México D.F.</td>\n",
       "      <td>5021</td>\n",
       "      <td>Mexico</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID                        CustomerName   ContactName  \\\n",
       "0           1                 Alfreds Futterkiste  Maria Anders   \n",
       "1           2  Ana Trujillo Emparedados y helados  Ana Trujillo   \n",
       "\n",
       "                         Address         City PostalCode  Country  \n",
       "0                  Obere Str. 57       Berlin      12209  Germany  \n",
       "1  Avda. de la Constitución 2222  México D.F.       5021   Mexico  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>OrderID</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10248</td>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10249</td>\n",
       "      <td>81</td>\n",
       "      <td>6</td>\n",
       "      <td>1996-07-05</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OrderID  CustomerID  EmployeeID   OrderDate  ShipperID\n",
       "0    10248          90           5  1996-07-04          3\n",
       "1    10249          81           6  1996-07-05          1"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>OrderDetailID</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10248</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>10248</td>\n",
       "      <td>42</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OrderDetailID  OrderID  ProductID  Quantity\n",
       "0              1    10248         11        12\n",
       "1              2    10248         42        10"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orderDetails.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>ProductID</th>\n",
       "      <th>ProductName</th>\n",
       "      <th>SupplierID</th>\n",
       "      <th>CategoryID</th>\n",
       "      <th>Unit</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Chais</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10 boxes x 20 bags</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Chang</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>24 - 12 oz bottles</td>\n",
       "      <td>19.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ProductID ProductName  SupplierID  CategoryID                Unit  Price\n",
       "0          1       Chais           1           1  10 boxes x 20 bags   18.0\n",
       "1          2       Chang           1           1  24 - 12 oz bottles   19.0"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 고객 별 주문수를 알려주세요."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nSELECT c.CustomerID\\n     , COUNT(o.OrderID) as num_orders\\nFROM Customers AS c\\n     LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID\\nGROUP BY c.CustomerID\\n'"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "SELECT c.CustomerID\n",
    "     , COUNT(o.OrderID) as num_orders\n",
    "FROM Customers AS c\n",
    "     LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID\n",
    "GROUP BY c.CustomerID\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* 여기에서 왜 LEFT JOIN이 필요한가? -> 주문을 하나도 하지 않은 고객이 있을 수 있기 때문이다. Orders 테이블에는 존재하지 않지만, Customers 테이블에만 있는 고객들이 있기 때문이다.\n",
    "* 왜 CustomerName이 아니라 CustomerID를 사용해 GROUP BY를 해야하는가? -> 동명이인이 있을 수 있기 때문이다. 이름과 다르게, CustomerID는 한 사람에 하나만 부여되는 ID입니다. 주민등록번호라고 생각해도 좋겠네요!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_order = customers.merge(orders, how='left', on='CustomerID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>CustomerID</th>\n",
       "      <th>CustomerName</th>\n",
       "      <th>ContactName</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alfreds Futterkiste</td>\n",
       "      <td>Maria Anders</td>\n",
       "      <td>Obere Str. 57</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>12209</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ana Trujillo Emparedados y helados</td>\n",
       "      <td>Ana Trujillo</td>\n",
       "      <td>Avda. de la Constitución 2222</td>\n",
       "      <td>México D.F.</td>\n",
       "      <td>5021</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>10308.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1996-09-18</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID                        CustomerName   ContactName  \\\n",
       "0           1                 Alfreds Futterkiste  Maria Anders   \n",
       "1           2  Ana Trujillo Emparedados y helados  Ana Trujillo   \n",
       "\n",
       "                         Address         City PostalCode  Country  OrderID  \\\n",
       "0                  Obere Str. 57       Berlin      12209  Germany      NaN   \n",
       "1  Avda. de la Constitución 2222  México D.F.       5021   Mexico  10308.0   \n",
       "\n",
       "   EmployeeID   OrderDate  ShipperID  \n",
       "0         NaN         NaN        NaN  \n",
       "1         7.0  1996-09-18        3.0  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 1번 고객은 Orders 테이블에 정보가 없는가보군요. -> 주문을 한 번도 안 함.\n",
    "customers_order.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q1. 1번 고객이 진짜 없는지 확인 해 볼까요?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>OrderID</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [OrderID, CustomerID, EmployeeID, OrderDate, ShipperID]\n",
       "Index: []"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders[orders['CustomerID'] == 1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q2. 고객 중 동명이인이 있나요?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1])"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers['CustomerName'].value_counts().unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 다시 문제로 돌아옵시다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# size()와 count()의 차이\n",
    "# customers_num_orders = customers_order.groupby('CustomerID')['OrderID'].size()\n",
    "customers_num_orders = customers_order.groupby('CustomerID')['OrderID'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# value_counts()는 size()와 count() 중 어떤 것과 비슷할까?\n",
    "# customers_order['CustomerID'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1996년 10월 동안 고객 별 주문 수를 계산하세요."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 두 쿼리 중 어떤 쿼리가 맞는 결과를 출력할까요?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nSELECT c.CustomerID\\n     , COUNT(o.OrderID)\\nFROM Customers AS c\\n     LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID \\n                           AND o.OrderDate BETWEEN '1996-10-01' AND '1996-10-31'\\nGROUP BY c.CustomerID\\n\""
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "SELECT c.CustomerID\n",
    "     , COUNT(o.OrderID)\n",
    "FROM Customers AS c\n",
    "     LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID \n",
    "                           AND o.OrderDate BETWEEN '1996-10-01' AND '1996-10-31'\n",
    "GROUP BY c.CustomerID\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nSELECT c.CustomerID\\n     , COUNT(o.OrderID)\\nFROM Customers AS c\\n     LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID \\nWHERE o.OrderDate BETWEEN '1996-10-01' AND '1996-10-31'\\nGROUP BY c.CustomerID\\n\""
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "SELECT c.CustomerID\n",
    "     , COUNT(o.OrderID)\n",
    "FROM Customers AS c\n",
    "     LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID \n",
    "WHERE o.OrderDate BETWEEN '1996-10-01' AND '1996-10-31'\n",
    "GROUP BY c.CustomerID\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 10월 중 주문만 뽑기"
   ]
  },
  {
   "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>OrderID</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OrderDate</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1996-10-01</th>\n",
       "      <td>10318</td>\n",
       "      <td>38</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-10-02</th>\n",
       "      <td>10319</td>\n",
       "      <td>80</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            OrderID  CustomerID  EmployeeID  ShipperID\n",
       "OrderDate                                             \n",
       "1996-10-01    10318          38           8          2\n",
       "1996-10-02    10319          80           7          3"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_october = orders.set_index('OrderDate').loc['1996-10-01':'1996-10-31',:]\n",
    "orders_october.head(2)"
   ]
  },
  {
   "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>OrderDate</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-10-01</td>\n",
       "      <td>10318</td>\n",
       "      <td>38</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-10-02</td>\n",
       "      <td>10319</td>\n",
       "      <td>80</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    OrderDate  OrderID  CustomerID  EmployeeID  ShipperID\n",
       "0  1996-10-01    10318          38           8          2\n",
       "1  1996-10-02    10319          80           7          3"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_october = orders_october.reset_index()\n",
    "orders_october.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q3. 1996년 9월 주문만 뽑아서 orders_september에 저장해보세요."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_september = orders.set_index('OrderDate').loc['1996-09-01':'1996-09-30',:].reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 다시 원래 문제로 돌아와서,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_orders_october = customers.merge(orders_october, how='left', on='CustomerID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_num_orders_october = customers_orders_october.groupby('CustomerID')['OrderID'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CustomerID\n",
       "1     0\n",
       "63    0\n",
       "62    0\n",
       "61    0\n",
       "59    0\n",
       "     ..\n",
       "25    2\n",
       "51    2\n",
       "87    2\n",
       "38    2\n",
       "39    2\n",
       "Name: OrderID, Length: 91, dtype: int64"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_num_orders_october.sort_values()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q4. CustomerID 39번 손님은 1996년 10월 중에 2번 주문을 했네요. 확인 해볼까요?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>OrderID</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>10323</td>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>1996-10-07</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>10325</td>\n",
       "      <td>39</td>\n",
       "      <td>1</td>\n",
       "      <td>1996-10-09</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    OrderID  CustomerID  EmployeeID   OrderDate  ShipperID\n",
       "75    10323          39           4  1996-10-07          1\n",
       "77    10325          39           1  1996-10-09          3"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders[orders['CustomerID'] == 39]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 고객 별 주문금액을 알려주세요."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1. 주문 별 금액 계산"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_detail = orders.merge(orderDetails, how='inner', on='OrderID').merge(products, how='inner', on='ProductID')[['OrderID', 'CustomerID', 'OrderDate', 'OrderDetailID', 'Quantity', 'Price']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_detail['total_price'] = orders_detail['Quantity'] * orders_detail['Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_by_order = orders_detail.groupby(['OrderID', 'CustomerID', 'OrderDate'])['total_price'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>OrderID</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>total_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10248</td>\n",
       "      <td>90</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>566.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10249</td>\n",
       "      <td>81</td>\n",
       "      <td>1996-07-05</td>\n",
       "      <td>2329.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10250</td>\n",
       "      <td>34</td>\n",
       "      <td>1996-07-08</td>\n",
       "      <td>2267.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10251</td>\n",
       "      <td>84</td>\n",
       "      <td>1996-07-08</td>\n",
       "      <td>839.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10252</td>\n",
       "      <td>76</td>\n",
       "      <td>1996-07-09</td>\n",
       "      <td>4662.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OrderID  CustomerID   OrderDate  total_price\n",
       "0    10248          90  1996-07-04       566.00\n",
       "1    10249          81  1996-07-05      2329.25\n",
       "2    10250          34  1996-07-08      2267.25\n",
       "3    10251          84  1996-07-08       839.50\n",
       "4    10252          76  1996-07-09      4662.50"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_by_order.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "566.0"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 계산 결과가 의도대로 나왔는지 테스트\n",
    "orders_detail[orders_detail['OrderID'] == 10248]['total_price'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2. 고객 데이터와 붙이기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_price_by_order = customers.merge(price_by_order, how='left', on='CustomerID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>CustomerID</th>\n",
       "      <th>CustomerName</th>\n",
       "      <th>ContactName</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>total_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alfreds Futterkiste</td>\n",
       "      <td>Maria Anders</td>\n",
       "      <td>Obere Str. 57</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>12209</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ana Trujillo Emparedados y helados</td>\n",
       "      <td>Ana Trujillo</td>\n",
       "      <td>Avda. de la Constitución 2222</td>\n",
       "      <td>México D.F.</td>\n",
       "      <td>5021</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>10308.0</td>\n",
       "      <td>1996-09-18</td>\n",
       "      <td>111.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID                        CustomerName   ContactName  \\\n",
       "0           1                 Alfreds Futterkiste  Maria Anders   \n",
       "1           2  Ana Trujillo Emparedados y helados  Ana Trujillo   \n",
       "\n",
       "                         Address         City PostalCode  Country  OrderID  \\\n",
       "0                  Obere Str. 57       Berlin      12209  Germany      NaN   \n",
       "1  Avda. de la Constitución 2222  México D.F.       5021   Mexico  10308.0   \n",
       "\n",
       "    OrderDate  total_price  \n",
       "0         NaN          NaN  \n",
       "1  1996-09-18        111.0  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_price_by_order.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_price_by_order['total_price'] = customers_price_by_order['total_price'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_by_customers = customers_price_by_order.groupby('CustomerID')['total_price'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CustomerID\n",
       "1        0.00\n",
       "2      111.00\n",
       "3      504.00\n",
       "4     1723.75\n",
       "5     5406.90\n",
       "       ...   \n",
       "87    5872.50\n",
       "88    3020.00\n",
       "89    4416.00\n",
       "90     566.00\n",
       "91     573.75\n",
       "Name: total_price, Length: 91, dtype: float64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_by_customers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q5. 89번 고객이 총 주문한 금액은 4416불입니다. 결과가 맞는지 확인하세요."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4416.0"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_detail[orders_detail['CustomerID'] == 89]['total_price'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1996년 10월 동안 고객 별 주문 금액을 계산하세요. 10월 중 주문을 한 번도 하지 않은 고객 목록을 주세요."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1. 10월 주문 별 금액 계산"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_by_order_october = price_by_order.set_index('OrderDate').loc['1996-10-01':'1996-10-31',:].reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2. 고객 데이터와 붙이기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_price_by_order_october = customers.merge(price_by_order_october, how='left', on='CustomerID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>CustomerID</th>\n",
       "      <th>CustomerName</th>\n",
       "      <th>ContactName</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>total_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>51</td>\n",
       "      <td>Mère Paillarde</td>\n",
       "      <td>Jean Fresnière</td>\n",
       "      <td>43 rue St. Laurent</td>\n",
       "      <td>Montréal</td>\n",
       "      <td>H1J 1C3</td>\n",
       "      <td>Canada</td>\n",
       "      <td>1996-10-17</td>\n",
       "      <td>10332.0</td>\n",
       "      <td>2792.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CustomerID    CustomerName     ContactName             Address      City  \\\n",
       "54          51  Mère Paillarde  Jean Fresnière  43 rue St. Laurent  Montréal   \n",
       "\n",
       "   PostalCode Country   OrderDate  OrderID  total_price  \n",
       "54    H1J 1C3  Canada  1996-10-17  10332.0       2792.0  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_price_by_order_october.sample()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_price_by_order_october = customers_price_by_order_october.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_october = customers_price_by_order_october.groupby(['CustomerID', 'CustomerName'])['total_price'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CustomerID  CustomerName       \n",
       "44          Lehmanns Marktstand    1982.5\n",
       "Name: total_price, dtype: float64"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_october.sample()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 데이터가 맞는지 확인하기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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>CustomerID</th>\n",
       "      <th>CustomerName</th>\n",
       "      <th>ContactName</th>\n",
       "      <th>Address</th>\n",
       "      <th>City</th>\n",
       "      <th>PostalCode</th>\n",
       "      <th>Country</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>total_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>44</td>\n",
       "      <td>Lehmanns Marktstand</td>\n",
       "      <td>Renate Messner</td>\n",
       "      <td>Magazinweg 7</td>\n",
       "      <td>Frankfurt a.M.</td>\n",
       "      <td>60528</td>\n",
       "      <td>Germany</td>\n",
       "      <td>1996-10-31</td>\n",
       "      <td>10343.0</td>\n",
       "      <td>1982.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CustomerID         CustomerName     ContactName       Address  \\\n",
       "47          44  Lehmanns Marktstand  Renate Messner  Magazinweg 7   \n",
       "\n",
       "              City PostalCode  Country   OrderDate  OrderID  total_price  \n",
       "47  Frankfurt a.M.      60528  Germany  1996-10-31  10343.0       1982.5  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_price_by_order_october[customers_price_by_order_october['CustomerID'] == 44]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 바닥부터 확인하기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "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>OrderID</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>10279</td>\n",
       "      <td>44</td>\n",
       "      <td>8</td>\n",
       "      <td>1996-08-13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>10284</td>\n",
       "      <td>44</td>\n",
       "      <td>4</td>\n",
       "      <td>1996-08-19</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>10343</td>\n",
       "      <td>44</td>\n",
       "      <td>4</td>\n",
       "      <td>1996-10-31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    OrderID  CustomerID  EmployeeID   OrderDate  ShipperID\n",
       "31    10279          44           8  1996-08-13          2\n",
       "36    10284          44           4  1996-08-19          1\n",
       "95    10343          44           4  1996-10-31          1"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 이 중에서 1996년 10월에 주문한건 OrderID 10343\n",
    "orders[orders['CustomerID'] == 44]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>OrderDetailID</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>ProductName</th>\n",
       "      <th>SupplierID</th>\n",
       "      <th>CategoryID</th>\n",
       "      <th>Unit</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>256</td>\n",
       "      <td>10343</td>\n",
       "      <td>64</td>\n",
       "      <td>50</td>\n",
       "      <td>Wimmers gute Semmelknödel</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>20 bags x 4 pieces</td>\n",
       "      <td>33.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>257</td>\n",
       "      <td>10343</td>\n",
       "      <td>68</td>\n",
       "      <td>4</td>\n",
       "      <td>Scottish Longbreads</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "      <td>10 boxes x 8 pieces</td>\n",
       "      <td>12.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>258</td>\n",
       "      <td>10343</td>\n",
       "      <td>76</td>\n",
       "      <td>15</td>\n",
       "      <td>Lakkalikööri</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>500 ml</td>\n",
       "      <td>18.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OrderDetailID  OrderID  ProductID  Quantity                ProductName  \\\n",
       "0            256    10343         64        50  Wimmers gute Semmelknödel   \n",
       "1            257    10343         68         4        Scottish Longbreads   \n",
       "2            258    10343         76        15               Lakkalikööri   \n",
       "\n",
       "   SupplierID  CategoryID                 Unit  Price  \n",
       "0          12           5   20 bags x 4 pieces  33.25  \n",
       "1           8           3  10 boxes x 8 pieces  12.50  \n",
       "2          23           1               500 ml  18.00  "
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products_customer44 = orderDetails[orderDetails['OrderID'].isin([10343])].merge(products, on='ProductID', how='inner')\n",
    "products_customer44"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1982.5"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(products_customer44['Price'] * products_customer44['Quantity']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}