SQL & Pandas LEFT JOIN.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": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 데이터 읽기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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": 3,
   "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": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orderDetails.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 고객 별 주문수를 알려주세요."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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": 10,
     "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": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_order = customers.merge(orders, how='left', on='CustomerID')"
   ]
  },
  {
   "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>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": 12,
     "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": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q2. 고객 중 동명이인이 있나요?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 다시 문제로 돌아옵시다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CustomerID\n",
       "1      0\n",
       "26     0\n",
       "32     0\n",
       "40     0\n",
       "22     0\n",
       "      ..\n",
       "75     6\n",
       "65     7\n",
       "87     7\n",
       "63     7\n",
       "20    10\n",
       "Name: OrderID, Length: 91, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_num_orders.sort_values()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1996년 10월 동안 고객 별 주문 수를 계산하세요."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 두 쿼리 중 어떤 쿼리가 맞는 결과를 출력할까요?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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": 28,
     "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": 29,
   "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": 29,
     "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": [
    "# Answer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 다시 원래 문제로 돌아와서,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_orders_october = customers.merge(orders_october, how='left', on='CustomerID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_num_orders_october = customers_orders_october.groupby('CustomerID')['OrderID'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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": 64,
     "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": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 고객 별 주문금액을 알려주세요."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1. 주문 별 금액 계산"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders_detail['total_price'] = orders_detail['Quantity'] * orders_detail['Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_by_order = orders_detail.groupby(['OrderID', 'CustomerID', 'OrderDate'])['total_price'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "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": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_by_order.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "566.0"
      ]
     },
     "execution_count": 82,
     "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": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_price_by_order = customers.merge(price_by_order, how='left', on='CustomerID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers_price_by_order.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_price_by_order['total_price'] = customers_price_by_order['total_price'].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_by_customers = customers_price_by_order.groupby('CustomerID')['total_price'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_by_customers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q5. 89번 고객이 총 주문한 금액은 4416불입니다. 결과가 맞는지 확인하세요."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1996년 10월 동안 고객 별 주문 금액을 계산하세요. 10월 중 주문을 한 번도 하지 않은 고객 목록을 주세요."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 1. 10월 주문 별 금액 계산"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_by_order_october = price_by_order.set_index('OrderDate').loc['1996-10-01':'1996-10-31',:].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers_price_by_order_october = customers.merge(price_by_order_october, how='left', on='CustomerID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2. 고객 데이터와 붙이기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Answer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "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
}