Mastering Table.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 테이블 다루기\n",
    "- 테이블 형태의 데이터를 다양한 방법으로 합치는 방법을 배운다\n",
    "- concat, append, join, merge\n",
    "- groupby\n",
    "- pivot_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "%config InlineBackend.figure_format = 'retina'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## concat\n",
    "- 테이블을 합치는 가장 기본적인 방법으로, 행이나 열의 크기가 같을 때 주로 사용한다\n",
    "- 열방향(가로) 또는 행방향(세로)으로 합칠 수 있다. 즉, 컬럼을 늘리거나 샘플을 늘릴 때"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>990</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>350</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>250</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp\n",
       "0   서울         990    13\n",
       "1   부산         350    16\n",
       "2   대구         250    14\n",
       "3   대전         154    13\n",
       "4   광주         150    15"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data1 = {'city': ['서울', '부산', '대구', '대전', '광주'],\n",
    "        'population': [990, 350, 250, 154, 150],\n",
    "        'temp': [13, 16, 14, 13, 15]}\n",
    "\n",
    "df1 = pd.DataFrame(data1)\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 가로로 합치는 경우\n",
    "- 인덱스를 기준으로 합친다"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>city</th>\n",
       "      <th>cars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  cars\n",
       "0   서울   300\n",
       "1   부산   140\n",
       "2   대구   120\n",
       "3   대전    70\n",
       "4   광주    50"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2 = {'city': ['서울', '부산', '대구', '대전', '광주'],\n",
    "        'cars' : [300, 140, 120, 70, 50]}\n",
    "df2 = pd.DataFrame(data2)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>cars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cars\n",
       "0   300\n",
       "1   140\n",
       "2   120\n",
       "3    70\n",
       "4    50"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nocity = df2.drop('city', axis=1)\n",
    "df_nocity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>city</th>\n",
       "      <th>cars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>990</td>\n",
       "      <td>13</td>\n",
       "      <td>서울</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>350</td>\n",
       "      <td>16</td>\n",
       "      <td>부산</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>250</td>\n",
       "      <td>14</td>\n",
       "      <td>대구</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>대전</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "      <td>광주</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp city  cars\n",
       "0   서울         990    13   서울   300\n",
       "1   부산         350    16   부산   140\n",
       "2   대구         250    14   대구   120\n",
       "3   대전         154    13   대전    70\n",
       "4   광주         150    15   광주    50"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#가로로 붙이기\n",
    "df_new = pd.concat((df1, df2), axis=1)\n",
    "df_new"
   ]
  },
  {
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>cars</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>990</td>\n",
       "      <td>13</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>350</td>\n",
       "      <td>16</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>250</td>\n",
       "      <td>14</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp  cars\n",
       "0   서울         990    13   300\n",
       "1   부산         350    16   140\n",
       "2   대구         250    14   120\n",
       "3   대전         154    13    70\n",
       "4   광주         150    15    50"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_experiment = pd.concat((df1, df_nocity), axis=1)\n",
    "df_experiment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- (주의) 동일한 컬럼명(city)이 있으면 두 개 생긴다"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 세로로 합치는 경우\n",
    "- 즉, 샘플을 합칠 때"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>인천</td>\n",
       "      <td>290</td>\n",
       "      <td>12.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>울산</td>\n",
       "      <td>120</td>\n",
       "      <td>15.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp\n",
       "0   인천         290  12.7\n",
       "1   울산         120  15.5"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 열(컬럼)이 같은 데이터 생성\n",
    "data3 = {'city': ['인천', '울산'],\n",
    "        'population': [290, 120],\n",
    "        'temp': [12.7, 15.5]}\n",
    "df3 = pd.DataFrame(data3)\n",
    "df3"
   ]
  },
  {
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>990</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>350</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>250</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>인천</td>\n",
       "      <td>290</td>\n",
       "      <td>12.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>울산</td>\n",
       "      <td>120</td>\n",
       "      <td>15.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp\n",
       "0   서울         990  13.0\n",
       "1   부산         350  16.0\n",
       "2   대구         250  14.0\n",
       "3   대전         154  13.0\n",
       "4   광주         150  15.0\n",
       "0   인천         290  12.7\n",
       "1   울산         120  15.5"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 기존의 인덱스를 유지하면서 합치는 경우\n",
    "pd.concat((df1, df3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## append\n",
    "- 세로방향으로 샘플을 더할 때는 concat 대신에 append를 사용할 수 있다"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>인천</td>\n",
       "      <td>290</td>\n",
       "      <td>12.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>울산</td>\n",
       "      <td>120</td>\n",
       "      <td>15.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>990</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>350</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>250</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp\n",
       "0   인천         290  12.7\n",
       "1   울산         120  15.5\n",
       "0   서울         990  13.0\n",
       "1   부산         350  16.0\n",
       "2   대구         250  14.0\n",
       "3   대전         154  13.0\n",
       "4   광주         150  15.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.append(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## join\n",
    "- 행(Row)의 크기가 일치하지 않는 테이블을 가로 방향으로 붙이는 방법\n",
    "- 기본적으로 인덱스를 기준으로 테이블을 붙인다"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>population</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>서울</th>\n",
       "      <td>990</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>부산</th>\n",
       "      <td>350</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>대구</th>\n",
       "      <td>250</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>대전</th>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>광주</th>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      population  temp\n",
       "city                  \n",
       "서울           990    13\n",
       "부산           350    16\n",
       "대구           250    14\n",
       "대전           154    13\n",
       "광주           150    15"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame (data1).set_index('city')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>대전</th>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>광주</th>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>인천</th>\n",
       "      <td>31</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>울산</th>\n",
       "      <td>41</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      sale_1  sale_2\n",
       "city                \n",
       "대전        11      12\n",
       "광주        21      22\n",
       "인천        31      32\n",
       "울산        41      42"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 인덱스와 컬럼 내용이 df1과 다른 새로운 데이터프레임 생성\n",
    "data4 = {'city':['대전', '광주','인천', '울산'],\n",
    "         'sale_1': [11, 21, 31, 41],\n",
    "         'sale_2': [12, 22, 32, 42]\n",
    "        }\n",
    "df4 = pd.DataFrame(data4).set_index('city')\n",
    "df4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 붙이는 두 데이터프레임의 인덱스가 일부만 겹칠 때"
   ]
  },
  {
   "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>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>서울</th>\n",
       "      <td>990</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>부산</th>\n",
       "      <td>350</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>대구</th>\n",
       "      <td>250</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>대전</th>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>광주</th>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      population  temp  sale_1  sale_2\n",
       "city                                  \n",
       "서울           990    13     NaN     NaN\n",
       "부산           350    16     NaN     NaN\n",
       "대구           250    14     NaN     NaN\n",
       "대전           154    13    11.0    12.0\n",
       "광주           150    15    21.0    22.0"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 디폴트로 좌측 데이터프레임의 인덱스를 기준으로 붙이며 인덱스가 빈 곳에는 nan이 들어간다\n",
    "df1.join(df4) #df1기준으로 df4의 정보를 df1에다가 붙인다.\n",
    "#NaN은 아무것도 없다고 없다."
   ]
  },
  {
   "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>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>서울</th>\n",
       "      <td>990</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>부산</th>\n",
       "      <td>350</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>대구</th>\n",
       "      <td>250</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>대전</th>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>광주</th>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      population  temp  sale_1  sale_2\n",
       "city                                  \n",
       "서울           990    13     NaN     NaN\n",
       "부산           350    16     NaN     NaN\n",
       "대구           250    14     NaN     NaN\n",
       "대전           154    13    11.0    12.0\n",
       "광주           150    15    21.0    22.0"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#위의 디폴트 동작과 같다 (좌측 기준)\n",
    "df1.join(df4, how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>대전</th>\n",
       "      <td>154.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>광주</th>\n",
       "      <td>150.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>인천</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>울산</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      population  temp  sale_1  sale_2\n",
       "city                                  \n",
       "대전         154.0  13.0      11      12\n",
       "광주         150.0  15.0      21      22\n",
       "인천           NaN   NaN      31      32\n",
       "울산           NaN   NaN      41      42"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 우측 데이터프레임의 인덱스를 기준으로 붙이는 경우\n",
    "df1.join(df4, how='right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>광주</th>\n",
       "      <td>150.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>대구</th>\n",
       "      <td>250.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>대전</th>\n",
       "      <td>154.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>부산</th>\n",
       "      <td>350.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>서울</th>\n",
       "      <td>990.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>울산</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>인천</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      population  temp  sale_1  sale_2\n",
       "city                                  \n",
       "광주         150.0  15.0    21.0    22.0\n",
       "대구         250.0  14.0     NaN     NaN\n",
       "대전         154.0  13.0    11.0    12.0\n",
       "부산         350.0  16.0     NaN     NaN\n",
       "서울         990.0  13.0     NaN     NaN\n",
       "울산           NaN   NaN    41.0    42.0\n",
       "인천           NaN   NaN    31.0    32.0"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 좌우 데이터프레임 인덱스 전체를 대상으로 붙이는 경우 \n",
    "df1.join(df4, how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>대전</th>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>광주</th>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      population  temp  sale_1  sale_2\n",
       "city                                  \n",
       "대전           154    13      11      12\n",
       "광주           150    15      21      22"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 좌우 데이터프레임 인덱스 중 공통부분을 대상으로 붙이는 경우\n",
    "df1.join(df4, how='inner')\n",
    "#교집합 느낌"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- join에서 두 데이터프레임에 같은 컬럼명이 있으면 오류 발생\n",
    " - 미리 한 쪽의 컬럼명을 바꾸고 join해야 한다"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## merge\n",
    "- 특정 열(컬럼)을 기준으로 데이터프레임 합치기\n",
    "- 즉, 인덱스를 사용하지 않는다\n",
    "- join 은 index 기준, (맨 왼쪽 label), merge는 specific column 기준을 잡고 합친다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>990</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>350</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>250</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp\n",
       "0   서울         990    13\n",
       "1   부산         350    16\n",
       "2   대구         250    14\n",
       "3   대전         154    13\n",
       "4   광주         150    15"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# city를 인덱스로 사용하지 않는 데이터프레임 생성\n",
    "df1 = pd.DataFrame(data1)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>city</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>대전</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>광주</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>인천</td>\n",
       "      <td>31</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>울산</td>\n",
       "      <td>41</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  sale_1  sale_2\n",
       "0   대전      11      12\n",
       "1   광주      21      22\n",
       "2   인천      31      32\n",
       "3   울산      41      42"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# city를 인덱스로 사용하지 않는 데이터프레임 생성\n",
    "df4 = pd.DataFrame(data4)\n",
    "df4"
   ]
  },
  {
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp  sale_1  sale_2\n",
       "0   대전         154    13      11      12\n",
       "1   광주         150    15      21      22"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 특정 컬럼 city를 기준으로 합친다\n",
    "df1.merge(df4, on='city', how='inner')"
   ]
  },
  {
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp  sale_1  sale_2\n",
       "0   대전         154    13      11      12\n",
       "1   광주         150    15      21      22"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 위의 옵션은 디폴트 통작이다 (on과 how를 지정하지 않는 경우)\n",
    "df1.merge(df4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>990</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>350</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>250</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>150</td>\n",
       "      <td>15</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp  sale_1  sale_2\n",
       "0   서울         990    13     NaN     NaN\n",
       "1   부산         350    16     NaN     NaN\n",
       "2   대구         250    14     NaN     NaN\n",
       "3   대전         154    13    11.0    12.0\n",
       "4   광주         150    15    21.0    22.0"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 좌측 데이터프레임의 컬럼 city를 기준으로 합치는 경우\n",
    "df1.merge(df4, on='city', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>대전</td>\n",
       "      <td>154.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>광주</td>\n",
       "      <td>150.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>인천</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>울산</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp  sale_1  sale_2\n",
       "0   대전       154.0  13.0      11      12\n",
       "1   광주       150.0  15.0      21      22\n",
       "2   인천         NaN   NaN      31      32\n",
       "3   울산         NaN   NaN      41      42"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 우측 데이터프레임의 컬럼 city를 기준으로 합치는 경우\n",
    "df1.merge(df4, on='city', how='right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>city</th>\n",
       "      <th>population</th>\n",
       "      <th>temp</th>\n",
       "      <th>sale_1</th>\n",
       "      <th>sale_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>서울</td>\n",
       "      <td>990.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>부산</td>\n",
       "      <td>350.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>대구</td>\n",
       "      <td>250.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>대전</td>\n",
       "      <td>154.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>광주</td>\n",
       "      <td>150.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>인천</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>울산</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>41.0</td>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  city  population  temp  sale_1  sale_2\n",
       "0   서울       990.0  13.0     NaN     NaN\n",
       "1   부산       350.0  16.0     NaN     NaN\n",
       "2   대구       250.0  14.0     NaN     NaN\n",
       "3   대전       154.0  13.0    11.0    12.0\n",
       "4   광주       150.0  15.0    21.0    22.0\n",
       "5   인천         NaN   NaN    31.0    32.0\n",
       "6   울산         NaN   NaN    41.0    42.0"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 전체 데이터프레임의 컬럼 city를 기준으로 합치는 경우\n",
    "df1.merge(df4, on='city', how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 테이블 합치기 예"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>24</td>\n",
       "      <td>34</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>35</td>\n",
       "      <td>45</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>23</td>\n",
       "      <td>30</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item logistic  2019  2020 region\n",
       "0  eCar     ship    19    21     US\n",
       "1  eCar      air    27   102     US\n",
       "2    TV     ship    24    34     US\n",
       "3    TV      air    13    87     US\n",
       "4  Refr     ship    35    45     US\n",
       "5  Refr      air    23    30     US"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 전자제품 미국 수출 데이터, logistic은 운송 방법\n",
    "x1 = {'item': ['eCar', 'eCar', 'TV',  'TV', 'Refr','Refr'],\n",
    "        'logistic': ['ship', 'air', 'ship', 'air', 'ship', 'air'],\n",
    "        '2019': [19, 27, 24, 13, 35, 23],\n",
    "        '2020': [21, 102, 34, 87, 45, 30]}\n",
    "dx1 = pd.DataFrame(x1)\n",
    "dx1['region']='US'\n",
    "dx1"
   ]
  },
  {
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>45</td>\n",
       "      <td>56</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>48</td>\n",
       "      <td>98</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>65</td>\n",
       "      <td>79</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item logistic  2019  2020 region\n",
       "0  Refr     ship    29   180  China\n",
       "1  Refr      air    56    42  China\n",
       "2    TV     ship    45    56  China\n",
       "3    TV      air    56    89  China\n",
       "4  eCar     ship    48    98  China\n",
       "5  eCar      air    65    79  China"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 중국 수출 데이터, logistic은 운송 방법\n",
    "x2 = {'item': [ 'Refr', 'Refr', 'TV', 'TV', 'eCar', 'eCar'],\n",
    "        'logistic': ['ship', 'air', 'ship', 'air', 'ship', 'air'],\n",
    "        '2019': [29, 56, 45, 56, 48, 65],\n",
    "        '2020': [180, 42, 56, 89, 98, 79]}\n",
    "dx2 = pd.DataFrame(x2)\n",
    "dx2['region']='China'\n",
    "dx2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 샘플 합치기, concat 사용"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>24</td>\n",
       "      <td>34</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>35</td>\n",
       "      <td>45</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>23</td>\n",
       "      <td>30</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>45</td>\n",
       "      <td>56</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>48</td>\n",
       "      <td>98</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>65</td>\n",
       "      <td>79</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    item logistic  2019  2020 region\n",
       "0   eCar     ship    19    21     US\n",
       "1   eCar      air    27   102     US\n",
       "2     TV     ship    24    34     US\n",
       "3     TV      air    13    87     US\n",
       "4   Refr     ship    35    45     US\n",
       "5   Refr      air    23    30     US\n",
       "6   Refr     ship    29   180  China\n",
       "7   Refr      air    56    42  China\n",
       "8     TV     ship    45    56  China\n",
       "9     TV      air    56    89  China\n",
       "10  eCar     ship    48    98  China\n",
       "11  eCar      air    65    79  China"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 컬럼이 일치하므로 concat으로 샘플을 합칠 수 있다 (append도 같은 동작을 한다)\n",
    "df = pd.concat([dx1, dx2], ignore_index=True)\n",
    "# df = dx1.append(dx2, ignore_index=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 필터링\n",
    "- 특정 조건을 만족하는 데이터를 추출하는 것"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>24</td>\n",
       "      <td>34</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>35</td>\n",
       "      <td>45</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>23</td>\n",
       "      <td>30</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item logistic  2019  2020 region\n",
       "0  eCar     ship    19    21     US\n",
       "1  eCar      air    27   102     US\n",
       "2    TV     ship    24    34     US\n",
       "3    TV      air    13    87     US\n",
       "4  Refr     ship    35    45     US\n",
       "5  Refr      air    23    30     US"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 지역 필터링\n",
    "df[df['region']=='US']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>35</td>\n",
       "      <td>45</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>23</td>\n",
       "      <td>30</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item logistic  2019  2020 region\n",
       "4  Refr     ship    35    45     US\n",
       "5  Refr      air    23    30     US\n",
       "6  Refr     ship    29   180  China\n",
       "7  Refr      air    56    42  China"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 상품 필터링\n",
    "df[df['item']=='Refr']"
   ]
  },
  {
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>24</td>\n",
       "      <td>34</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>45</td>\n",
       "      <td>56</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>48</td>\n",
       "      <td>98</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>65</td>\n",
       "      <td>79</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    item logistic  2019  2020 region\n",
       "0   eCar     ship    19    21     US\n",
       "1   eCar      air    27   102     US\n",
       "2     TV     ship    24    34     US\n",
       "3     TV      air    13    87     US\n",
       "8     TV     ship    45    56  China\n",
       "9     TV      air    56    89  China\n",
       "10  eCar     ship    48    98  China\n",
       "11  eCar      air    65    79  China"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#두가지 아이템 필터링\n",
    "df[df['item'].isin(['TV', 'eCar'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item logistic  2019  2020 region\n",
       "6  Refr     ship    29   180  China\n",
       "7  Refr      air    56    42  China"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#두가지 조건을 만족하는 필터링\n",
    "df[(df['region']=='China') & (df['item']=='Refr')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 참조 테이블과 합치기\n",
    "- 상품별 가격표를 합치는 방법\n",
    "- merge 사용"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>item</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Refr</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   item  price\n",
       "0  Refr    200\n",
       "1  eCar   1000\n",
       "2    TV    100"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 가격표\n",
    "price_list = {'item': [ 'Refr', 'eCar', 'TV'],\n",
    "        'price': [200, 1000, 100]}\n",
    "df_price = pd.DataFrame(price_list)\n",
    "df_price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>US</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "      <td>US</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>48</td>\n",
       "      <td>98</td>\n",
       "      <td>China</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>65</td>\n",
       "      <td>79</td>\n",
       "      <td>China</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>24</td>\n",
       "      <td>34</td>\n",
       "      <td>US</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "      <td>US</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>45</td>\n",
       "      <td>56</td>\n",
       "      <td>China</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "      <td>China</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>35</td>\n",
       "      <td>45</td>\n",
       "      <td>US</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>23</td>\n",
       "      <td>30</td>\n",
       "      <td>US</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "      <td>China</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>China</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    item logistic  2019  2020 region  price\n",
       "0   eCar     ship    19    21     US   1000\n",
       "1   eCar      air    27   102     US   1000\n",
       "2   eCar     ship    48    98  China   1000\n",
       "3   eCar      air    65    79  China   1000\n",
       "4     TV     ship    24    34     US    100\n",
       "5     TV      air    13    87     US    100\n",
       "6     TV     ship    45    56  China    100\n",
       "7     TV      air    56    89  China    100\n",
       "8   Refr     ship    35    45     US    200\n",
       "9   Refr      air    23    30     US    200\n",
       "10  Refr     ship    29   180  China    200\n",
       "11  Refr      air    56    42  China    200"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 가격표를 반영한(합친) 데이터프레임\n",
    "# item 컬럼을 기준으로 합친다\n",
    "df_all = df.merge(df_price, on=\"item\")\n",
    "df_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "      <th>price</th>\n",
       "      <th>2020_sale</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>US</td>\n",
       "      <td>1000</td>\n",
       "      <td>21000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "      <td>US</td>\n",
       "      <td>1000</td>\n",
       "      <td>102000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>48</td>\n",
       "      <td>98</td>\n",
       "      <td>China</td>\n",
       "      <td>1000</td>\n",
       "      <td>98000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>65</td>\n",
       "      <td>79</td>\n",
       "      <td>China</td>\n",
       "      <td>1000</td>\n",
       "      <td>79000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>24</td>\n",
       "      <td>34</td>\n",
       "      <td>US</td>\n",
       "      <td>100</td>\n",
       "      <td>3400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "      <td>US</td>\n",
       "      <td>100</td>\n",
       "      <td>8700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>45</td>\n",
       "      <td>56</td>\n",
       "      <td>China</td>\n",
       "      <td>100</td>\n",
       "      <td>5600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "      <td>China</td>\n",
       "      <td>100</td>\n",
       "      <td>8900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>35</td>\n",
       "      <td>45</td>\n",
       "      <td>US</td>\n",
       "      <td>200</td>\n",
       "      <td>9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>23</td>\n",
       "      <td>30</td>\n",
       "      <td>US</td>\n",
       "      <td>200</td>\n",
       "      <td>6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "      <td>China</td>\n",
       "      <td>200</td>\n",
       "      <td>36000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>China</td>\n",
       "      <td>200</td>\n",
       "      <td>8400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    item logistic  2019  2020 region  price  2020_sale\n",
       "0   eCar     ship    19    21     US   1000      21000\n",
       "1   eCar      air    27   102     US   1000     102000\n",
       "2   eCar     ship    48    98  China   1000      98000\n",
       "3   eCar      air    65    79  China   1000      79000\n",
       "4     TV     ship    24    34     US    100       3400\n",
       "5     TV      air    13    87     US    100       8700\n",
       "6     TV     ship    45    56  China    100       5600\n",
       "7     TV      air    56    89  China    100       8900\n",
       "8   Refr     ship    35    45     US    200       9000\n",
       "9   Refr      air    23    30     US    200       6000\n",
       "10  Refr     ship    29   180  China    200      36000\n",
       "11  Refr      air    56    42  China    200       8400"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 새로운 컬럼 추가하기 (예: 상품별 매출액)\n",
    "df_all['2020_sale'] = df_all['2020'] * df_all['price']\n",
    "df_all"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 특정 조건 값 계산하기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "319"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2020년 미국 수출 총액\n",
    "df[df.region=='US']['2020'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "71.5"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2020년 항공 수출 평균액\n",
    "df[df.logistic=='air']['2020'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#2020년 전기차, 항공 수출 건수\n",
    "df[(df.item=='eCar')&(df.logistic=='air')]['2020'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# groupby\n",
    "- 특정 조건에 맞는 그룹을 테이블로 새로 만들고 다양하게 원하는 조작/계산을 한다\n",
    "- Powerful tool/ **RECOMMEND!!**"
   ]
  },
  {
   "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>2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Refr</th>\n",
       "      <td>297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV</th>\n",
       "      <td>266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eCar</th>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      2020\n",
       "item      \n",
       "Refr   297\n",
       "TV     266\n",
       "eCar   300"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 품목별 2020 매출 합계 보기\n",
    "df.groupby('item')['2020'].sum().to_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 품목별로 2019, 2020 각각에 대해 수출 합계, 건수, 평균치 보기\n",
    " - agg(aggregate)를 사용하여 여러개의 함수 이름을 나열한다 (임의로 정의한 함수를 사용할 수도 있다)\n",
    " - size는 항목의 갯수를 알려준다"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">2019</th>\n",
       "      <th colspan=\"3\" halign=\"left\">2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>size</th>\n",
       "      <th>mean</th>\n",
       "      <th>sum</th>\n",
       "      <th>size</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Refr</th>\n",
       "      <td>143</td>\n",
       "      <td>4</td>\n",
       "      <td>35.75</td>\n",
       "      <td>297</td>\n",
       "      <td>4</td>\n",
       "      <td>74.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV</th>\n",
       "      <td>138</td>\n",
       "      <td>4</td>\n",
       "      <td>34.50</td>\n",
       "      <td>266</td>\n",
       "      <td>4</td>\n",
       "      <td>66.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eCar</th>\n",
       "      <td>159</td>\n",
       "      <td>4</td>\n",
       "      <td>39.75</td>\n",
       "      <td>300</td>\n",
       "      <td>4</td>\n",
       "      <td>75.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     2019             2020            \n",
       "      sum size   mean  sum size   mean\n",
       "item                                  \n",
       "Refr  143    4  35.75  297    4  74.25\n",
       "TV    138    4  34.50  266    4  66.50\n",
       "eCar  159    4  39.75  300    4  75.00"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('item')[['2019','2020']].agg(['sum','size','mean'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "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></th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Refr</th>\n",
       "      <th>China</th>\n",
       "      <td>111.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>37.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">TV</th>\n",
       "      <th>China</th>\n",
       "      <td>72.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>60.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">eCar</th>\n",
       "      <th>China</th>\n",
       "      <td>88.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>61.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              2020\n",
       "item region       \n",
       "Refr China   111.0\n",
       "     US       37.5\n",
       "TV   China    72.5\n",
       "     US       60.5\n",
       "eCar China    88.5\n",
       "     US       61.5"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 품목별, 지역별로 2020년 판매 평균치 보기\n",
    "df.groupby(['item','region'])['2020'].mean().to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th>region</th>\n",
       "      <th>logistic</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Refr</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">China</th>\n",
       "      <th>air</th>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ship</th>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">US</th>\n",
       "      <th>air</th>\n",
       "      <td>23</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ship</th>\n",
       "      <td>35</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">TV</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">China</th>\n",
       "      <th>air</th>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ship</th>\n",
       "      <td>45</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">US</th>\n",
       "      <th>air</th>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ship</th>\n",
       "      <td>24</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">eCar</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">China</th>\n",
       "      <th>air</th>\n",
       "      <td>65</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ship</th>\n",
       "      <td>48</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">US</th>\n",
       "      <th>air</th>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ship</th>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      2019  2020\n",
       "item region logistic            \n",
       "Refr China  air         56    42\n",
       "            ship        29   180\n",
       "     US     air         23    30\n",
       "            ship        35    45\n",
       "TV   China  air         56    89\n",
       "            ship        45    56\n",
       "     US     air         13    87\n",
       "            ship        24    34\n",
       "eCar China  air         65    79\n",
       "            ship        48    98\n",
       "     US     air         27   102\n",
       "            ship        19    21"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 품목별, 지역별, 운송별로 2019, 2020년 판매 합계 보기\n",
    "df.groupby(['item', 'region', 'logistic'])[['2019', '2020']].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 조건에 맞는 그룹을 각각 얻기\n",
    "\n",
    "- groupby()의 실행 결과는 그룹명과 데이터프레임 두 가지를 리턴한다\n",
    "- 특정 그룹만 얻으려면 get_group()를 사용한다"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>24</td>\n",
       "      <td>34</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TV</td>\n",
       "      <td>ship</td>\n",
       "      <td>45</td>\n",
       "      <td>56</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  item logistic  2019  2020 region\n",
       "2   TV     ship    24    34     US\n",
       "3   TV      air    13    87     US\n",
       "8   TV     ship    45    56  China\n",
       "9   TV      air    56    89  China"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# item 기준으로 그룹을 나누고 그중 TV에 해당하는 내용만 별도의 데이터프레임으로 얻는다\n",
    "df.groupby(['item']).get_group('TV')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "# item 기준으로 나눈 그룹을 각각 별도의 csv 파일로 저장하는 예\n",
    "# group is dataframe\n",
    "for item, group in df.groupby('item'):\n",
    "    #print(group.mean())\n",
    "    group.to_csv(item + '.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-105-e0d08ae92c5a>, line 4)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-105-e0d08ae92c5a>\"\u001b[1;36m, line \u001b[1;32m4\u001b[0m\n\u001b[1;33m    dir*.csv\u001b[0m\n\u001b[1;37m        ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "# TV.csv, Refr.csv, eCar.csv 파일이 생성됨\n",
    "#!ls *.csv\n",
    "# 윈도우에서 보기\n",
    "dir*.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 조건에 맞는 그룹 찾기"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "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>2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Refr</th>\n",
       "      <td>74.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV</th>\n",
       "      <td>66.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eCar</th>\n",
       "      <td>75.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       2020\n",
       "item       \n",
       "Refr  74.25\n",
       "TV    66.50\n",
       "eCar  75.00"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 품목별 2020년 판매 평균값\n",
    "df.groupby(['item'])['2020'].mean().to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>19</td>\n",
       "      <td>21</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>35</td>\n",
       "      <td>45</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>23</td>\n",
       "      <td>30</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Refr</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>42</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>48</td>\n",
       "      <td>98</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>65</td>\n",
       "      <td>79</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    item logistic  2019  2020 region\n",
       "0   eCar     ship    19    21     US\n",
       "1   eCar      air    27   102     US\n",
       "4   Refr     ship    35    45     US\n",
       "5   Refr      air    23    30     US\n",
       "6   Refr     ship    29   180  China\n",
       "7   Refr      air    56    42  China\n",
       "10  eCar     ship    48    98  China\n",
       "11  eCar      air    65    79  China"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2020년 품목별 평균이 70을 넘는 \"상품\"들의 샘플을 모두 찾기 (여기서는 eCar와 Refr 두 상품이다)\n",
    "# filter를 사용하면 편리하다\n",
    "# filter 수행 결과는 True/False를 얻으며 해당 샘플을 추출한다\n",
    "df.groupby('item').filter(\n",
    "    lambda x : x['2020'].mean() > 70 \n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- (주의!) 아래는 2020년 매출액이 각 \"샘플별로\" 70보다 큰 경우를 찾는다 (위의 결과와 다르다)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "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>item</th>\n",
       "      <th>logistic</th>\n",
       "      <th>2019</th>\n",
       "      <th>2020</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>27</td>\n",
       "      <td>102</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>13</td>\n",
       "      <td>87</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Refr</td>\n",
       "      <td>ship</td>\n",
       "      <td>29</td>\n",
       "      <td>180</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TV</td>\n",
       "      <td>air</td>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>eCar</td>\n",
       "      <td>ship</td>\n",
       "      <td>48</td>\n",
       "      <td>98</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>eCar</td>\n",
       "      <td>air</td>\n",
       "      <td>65</td>\n",
       "      <td>79</td>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    item logistic  2019  2020 region\n",
       "1   eCar      air    27   102     US\n",
       "3     TV      air    13    87     US\n",
       "6   Refr     ship    29   180  China\n",
       "9     TV      air    56    89  China\n",
       "10  eCar     ship    48    98  China\n",
       "11  eCar      air    65    79  China"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df[\"2020\"] > 70]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pivot_table\n",
    "- 조건에 맞는 수치 요약 테이블을 생성한다\n",
    "- groupby는 그룹을 생성하는 중간 과정이 있으나 피봇 페이블은 수치 요약 결과만 보여준다"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "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>2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Refr</th>\n",
       "      <td>297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TV</th>\n",
       "      <td>266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eCar</th>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      2020\n",
       "item      \n",
       "Refr   297\n",
       "TV     266\n",
       "eCar   300"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 상품별 2020년 총 매출액\n",
    "df.pivot_table(index=['item'], values=['2020'], aggfunc='sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>2020</th>\n",
       "      <th>2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Refr</th>\n",
       "      <th>China</th>\n",
       "      <td>222</td>\n",
       "      <td>111.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>75</td>\n",
       "      <td>37.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">TV</th>\n",
       "      <th>China</th>\n",
       "      <td>145</td>\n",
       "      <td>72.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>121</td>\n",
       "      <td>60.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">eCar</th>\n",
       "      <th>China</th>\n",
       "      <td>177</td>\n",
       "      <td>88.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>123</td>\n",
       "      <td>61.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             sum   mean\n",
       "            2020   2020\n",
       "item region            \n",
       "Refr China   222  111.0\n",
       "     US       75   37.5\n",
       "TV   China   145   72.5\n",
       "     US      121   60.5\n",
       "eCar China   177   88.5\n",
       "     US      123   61.5"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 상품, 지역별 2020년 매출 합계와 평균 보기\n",
    "df.pivot_table(index=['item', 'region'], values=['2020'], aggfunc=['sum','mean'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">sum</th>\n",
       "      <th colspan=\"2\" halign=\"left\">mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">2020</th>\n",
       "      <th colspan=\"2\" halign=\"left\">2020</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>logistic</th>\n",
       "      <th>air</th>\n",
       "      <th>ship</th>\n",
       "      <th>air</th>\n",
       "      <th>ship</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item</th>\n",
       "      <th>region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Refr</th>\n",
       "      <th>China</th>\n",
       "      <td>42</td>\n",
       "      <td>180</td>\n",
       "      <td>42</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>30</td>\n",
       "      <td>45</td>\n",
       "      <td>30</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">TV</th>\n",
       "      <th>China</th>\n",
       "      <td>89</td>\n",
       "      <td>56</td>\n",
       "      <td>89</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>87</td>\n",
       "      <td>34</td>\n",
       "      <td>87</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">eCar</th>\n",
       "      <th>China</th>\n",
       "      <td>79</td>\n",
       "      <td>98</td>\n",
       "      <td>79</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>102</td>\n",
       "      <td>21</td>\n",
       "      <td>102</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             sum      mean     \n",
       "            2020      2020     \n",
       "logistic     air ship  air ship\n",
       "item region                    \n",
       "Refr China    42  180   42  180\n",
       "     US       30   45   30   45\n",
       "TV   China    89   56   89   56\n",
       "     US       87   34   87   34\n",
       "eCar China    79   98   79   98\n",
       "     US      102   21  102   21"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# columns을 지정하면 컬럼을 세분화하여 보여준다\n",
    "# 위의 결과를 운송 수단별로 더 세분화하여 보기\n",
    "df.pivot_table(index=['item', 'region'], values=['2020'],aggfunc=['sum','mean'],\n",
    "              columns=['logistic'])"
   ]
  },
  {
   "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
}