2019-09-09 Pandas 수업 보충자료.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "orders = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Orders.csv')\n",
    "orderDetails = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/OrderDetails.csv')\n",
    "products = pd.read_csv('https://raw.githubusercontent.com/dataitgirls3/Data/master/Products.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas merge() 함수의 다양한 옵션"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>OrderID</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "      <th>OrderDetailID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10248</td>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10248</td>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>42</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OrderID  CustomerID  EmployeeID   OrderDate  ShipperID  OrderDetailID  \\\n",
       "0    10248          90           5  1996-07-04          3              1   \n",
       "1    10248          90           5  1996-07-04          3              2   \n",
       "\n",
       "   ProductID  Quantity  \n",
       "0         11        12  \n",
       "1         42        10  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 조인하려는 두 테이블의 기준 컬럼 이름이 같을 때\n",
    "orders.merge(orderDetails, how='inner', on='OrderID').head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "      <td>10248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>81</td>\n",
       "      <td>6</td>\n",
       "      <td>1996-07-05</td>\n",
       "      <td>1</td>\n",
       "      <td>10249</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID  EmployeeID   OrderDate  ShipperID     ID\n",
       "0          90           5  1996-07-04          3  10248\n",
       "1          81           6  1996-07-05          1  10249"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 조인하려는 두 테이블의 기준 컬럼 이름이 다를 때\n",
    "# merge함수의 left_on, right_on 옵션을 활용한다.\n",
    "orders['ID'] = orders['OrderID']\n",
    "orders = orders.drop('OrderID', axis=1)\n",
    "orders.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "      <th>ID</th>\n",
       "      <th>OrderDetailID</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "      <td>10248</td>\n",
       "      <td>1</td>\n",
       "      <td>10248</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "      <td>10248</td>\n",
       "      <td>2</td>\n",
       "      <td>10248</td>\n",
       "      <td>42</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID  EmployeeID   OrderDate  ShipperID     ID  OrderDetailID  \\\n",
       "0          90           5  1996-07-04          3  10248              1   \n",
       "1          90           5  1996-07-04          3  10248              2   \n",
       "\n",
       "   OrderID  ProductID  Quantity  \n",
       "0    10248         11        12  \n",
       "1    10248         42        10  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.merge(orderDetails, how='inner', left_on='ID', right_on='OrderID').head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10248</th>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10249</th>\n",
       "      <td>81</td>\n",
       "      <td>6</td>\n",
       "      <td>1996-07-05</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CustomerID  EmployeeID   OrderDate  ShipperID\n",
       "ID                                                  \n",
       "10248          90           5  1996-07-04          3\n",
       "10249          81           6  1996-07-05          1"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 컬럼 대신 인덱스를 조인키로 활용해야 할 때\n",
    "# merge함수의 left_index, right_index 옵션을 활용한다.\n",
    "orders = orders.set_index('ID')\n",
    "orders.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "      <th>OrderDetailID</th>\n",
       "      <th>OrderID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>10248</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>10248</td>\n",
       "      <td>42</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID  EmployeeID   OrderDate  ShipperID  OrderDetailID  OrderID  \\\n",
       "0          90           5  1996-07-04          3              1    10248   \n",
       "1          90           5  1996-07-04          3              2    10248   \n",
       "\n",
       "   ProductID  Quantity  \n",
       "0         11        12  \n",
       "1         42        10  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders.merge(orderDetails, left_index=True, right_on='OrderID').head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas Series, DataFrame에서의 정렬"
   ]
  },
  {
   "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>CustomerID</th>\n",
       "      <th>EmployeeID</th>\n",
       "      <th>OrderDate</th>\n",
       "      <th>ShipperID</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10374</th>\n",
       "      <td>91</td>\n",
       "      <td>1</td>\n",
       "      <td>1996-12-05</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10248</th>\n",
       "      <td>90</td>\n",
       "      <td>5</td>\n",
       "      <td>1996-07-04</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       CustomerID  EmployeeID   OrderDate  ShipperID\n",
       "ID                                                  \n",
       "10374          91           1  1996-12-05          3\n",
       "10248          90           5  1996-07-04          3"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sort_values()를 이용한 Pandas DataFrame에서의 정렬\n",
    "# 데이터프레임(테이블)에는 컬럼이 여러개 있으므로 \n",
    "# 어떤 컬럼을 기준으로 정렬할지 by= 옵션에서 선택해준다.\n",
    "orders.sort_values(by='CustomerID', ascending=False).head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "# sort_values()를 이용한 Pandas Series에서의 정렬\n",
    "print(type(orders['OrderDate']))\n",
    "s = orders['OrderDate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ID\n",
       "10443    1997-02-12\n",
       "10442    1997-02-11\n",
       "10441    1997-02-10\n",
       "10440    1997-02-10\n",
       "10439    1997-02-07\n",
       "            ...    \n",
       "10252    1996-07-09\n",
       "10251    1996-07-08\n",
       "10250    1996-07-08\n",
       "10249    1996-07-05\n",
       "10248    1996-07-04\n",
       "Name: OrderDate, Length: 196, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.sort_values(ascending=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}