class_12.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "o0gaG9g5SELk"
   },
   "source": [
    "# pandasによるデータ処理2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "-NcaojFoPkce"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "77RvrbiCSQVb"
   },
   "source": [
    "## 練習問題1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "t25dSE7oSTj5"
   },
   "source": [
    "以下のデータフレームを考える。 これは5名の学生がそれぞれの教科(国語、数学、英語）の得点を示したものである。\n",
    "\n",
    "<table>\n",
    "    <tr>\n",
    "        <th></th>\n",
    "        <th>J</th>\n",
    "        <th>M</th>\n",
    "        <th>E</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>AA</th>\n",
    "        <td>44</td>\n",
    "        <td>90</td>\n",
    "        <td>54</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>BB</th>\n",
    "        <td>78</td>\n",
    "        <td>76</td>\n",
    "        <td>79</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>CC</th>\n",
    "        <td>92</td>\n",
    "        <td>88</td>\n",
    "        <td>93</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>DD</th>\n",
    "        <td>54</td>\n",
    "        <td>55</td>\n",
    "        <td>81</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>EE</th>\n",
    "        <td>89</td>\n",
    "        <td>74</td>\n",
    "        <td>95</td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "このデータフレームmarksを作り、各教科ごとの順位を示したデータフレームを求めよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "bcr4EG_zPr2k",
    "outputId": "8a26f8cc-60c5-42ac-b56f-c2a632e3b0ac"
   },
   "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>J</th>\n",
       "      <th>M</th>\n",
       "      <th>E</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AA</th>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BB</th>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CC</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DD</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EE</th>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      J    M    E\n",
       "AA  5.0  1.0  5.0\n",
       "BB  3.0  3.0  4.0\n",
       "CC  1.0  2.0  2.0\n",
       "DD  4.0  5.0  3.0\n",
       "EE  2.0  4.0  1.0"
      ]
     },
     "execution_count": 4,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "marks = pd.DataFrame({'J': [44, 78, 92, 54, 89], 'M': [90, 76, 88, 55, 74], 'E': [54, 79, 93, 81, 95]}, index=['AA', 'BB', 'CC', 'DD', 'EE'])\n",
    "marks.rank(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ziq1qJhJSbpR"
   },
   "source": [
    "## 練習問題2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LgcCeDcrSejq"
   },
   "source": [
    "練習問題1のデータフレームを用い、各教科で最高点を取った者の名前を示したシリーズを求めよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "QZ1s6WV0QeKk",
    "outputId": "e2aed266-01f9-4706-9c4e-a8d43f786a10"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "J    CC\n",
       "M    AA\n",
       "E    EE\n",
       "dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "marks.idxmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "id": "mIIhzuvTRnmC"
   },
   "outputs": [],
   "source": [
    "import pandas_datareader.data as web\n",
    "all_data = {ticker: web.get_data_yahoo(ticker) for ticker in ['AAPL', 'IBM', 'MSFT', 'GOOG']}\n",
    "\n",
    "price = pd.DataFrame({ticker: data['Adj Close'] for ticker, data in all_data.items()})\n",
    "volume = pd.DataFrame({ticker: data['Volume'] for ticker, data in all_data.items()})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 235
    },
    "id": "z4SuEVnSSBJy",
    "outputId": "1694f68e-ccb3-4452-a888-6bffb148ec5a"
   },
   "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>AAPL</th>\n",
       "      <th>IBM</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>GOOG</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2021-06-24</th>\n",
       "      <td>-0.002169</td>\n",
       "      <td>0.005740</td>\n",
       "      <td>0.005353</td>\n",
       "      <td>0.006488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-25</th>\n",
       "      <td>-0.002249</td>\n",
       "      <td>0.009626</td>\n",
       "      <td>-0.006262</td>\n",
       "      <td>-0.002255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-28</th>\n",
       "      <td>0.012546</td>\n",
       "      <td>-0.010556</td>\n",
       "      <td>0.013961</td>\n",
       "      <td>-0.001382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-29</th>\n",
       "      <td>0.011500</td>\n",
       "      <td>0.001790</td>\n",
       "      <td>0.009973</td>\n",
       "      <td>-0.006316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021-06-30</th>\n",
       "      <td>0.004621</td>\n",
       "      <td>0.007145</td>\n",
       "      <td>-0.001842</td>\n",
       "      <td>-0.005575</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                AAPL       IBM      MSFT      GOOG\n",
       "Date                                              \n",
       "2021-06-24 -0.002169  0.005740  0.005353  0.006488\n",
       "2021-06-25 -0.002249  0.009626 -0.006262 -0.002255\n",
       "2021-06-28  0.012546 -0.010556  0.013961 -0.001382\n",
       "2021-06-29  0.011500  0.001790  0.009973 -0.006316\n",
       "2021-06-30  0.004621  0.007145 -0.001842 -0.005575"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "returns = price.pct_change()\n",
    "returns.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 173
    },
    "id": "OkmqCHIKSrId",
    "outputId": "0d50f6d1-beab-4028-988d-5d8ea81d08af"
   },
   "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>AAPL</th>\n",
       "      <th>IBM</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>GOOG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.442724</td>\n",
       "      <td>0.725843</td>\n",
       "      <td>0.658425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM</th>\n",
       "      <td>0.442724</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.523463</td>\n",
       "      <td>0.489079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MSFT</th>\n",
       "      <td>0.725843</td>\n",
       "      <td>0.523463</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.772842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOG</th>\n",
       "      <td>0.658425</td>\n",
       "      <td>0.489079</td>\n",
       "      <td>0.772842</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          AAPL       IBM      MSFT      GOOG\n",
       "AAPL  1.000000  0.442724  0.725843  0.658425\n",
       "IBM   0.442724  1.000000  0.523463  0.489079\n",
       "MSFT  0.725843  0.523463  1.000000  0.772842\n",
       "GOOG  0.658425  0.489079  0.772842  1.000000"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "returns.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 173
    },
    "id": "5etEy_0HSvpa",
    "outputId": "7ea80884-8c61-4684-e491-ddac0bf8d492"
   },
   "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>AAPL</th>\n",
       "      <th>IBM</th>\n",
       "      <th>MSFT</th>\n",
       "      <th>GOOG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AAPL</th>\n",
       "      <td>0.000363</td>\n",
       "      <td>0.000138</td>\n",
       "      <td>0.000238</td>\n",
       "      <td>0.000210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IBM</th>\n",
       "      <td>0.000138</td>\n",
       "      <td>0.000266</td>\n",
       "      <td>0.000147</td>\n",
       "      <td>0.000134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MSFT</th>\n",
       "      <td>0.000238</td>\n",
       "      <td>0.000147</td>\n",
       "      <td>0.000296</td>\n",
       "      <td>0.000223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOG</th>\n",
       "      <td>0.000210</td>\n",
       "      <td>0.000134</td>\n",
       "      <td>0.000223</td>\n",
       "      <td>0.000282</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          AAPL       IBM      MSFT      GOOG\n",
       "AAPL  0.000363  0.000138  0.000238  0.000210\n",
       "IBM   0.000138  0.000266  0.000147  0.000134\n",
       "MSFT  0.000238  0.000147  0.000296  0.000223\n",
       "GOOG  0.000210  0.000134  0.000223  0.000282"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "returns.cov()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "L_4ENEIVTNGh"
   },
   "source": [
    "## 練習問題3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4EWyIJmTTPkp"
   },
   "source": [
    "練習問題1のデータフレームで、国語と英語の得点の相関係数を求めよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "kpQ84qspSyhi",
    "outputId": "583b5bdb-c294-4fb6-e4ed-0e7658fa5463"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8712200243905397"
      ]
     },
     "execution_count": 17,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "marks.corrwith(marks['E'])['J']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "gI6W4lPkTWDB",
    "outputId": "9f3695f5-4a26-4b21-9e69-fde17e80533b"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8712200243905397"
      ]
     },
     "execution_count": 18,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "marks.corrwith(marks['J'])['E']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DjSnmTXYZulx"
   },
   "source": [
    "## 練習問題4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "wxRPgrJQZxAw"
   },
   "source": [
    "以下のような階層型インデックスを持つデータフレームを作成せよ。\n",
    "\n",
    "<table>\n",
    "    <tr>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th>Product_ID</th>\n",
    "        <th>Qty</th>\n",
    "        <th>Amonut</th>\n",
    "        <th>Tax</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Sales_No</th>\n",
    "        <th>Sales_Item_No</th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>A001</th>\n",
    "        <th>1</th>\n",
    "        <td>P001</td>\n",
    "        <td>1</td>\n",
    "        <td>7000</td>\n",
    "        <td>560</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th></th>\n",
    "        <th>2</th>\n",
    "        <td>P002</td>\n",
    "        <td>2</td>\n",
    "        <td>7700</td>\n",
    "        <td>616</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>A002</th>\n",
    "        <th>1</th>\n",
    "        <td>P003</td>\n",
    "        <td>1</td>\n",
    "        <td>6800</td>\n",
    "        <td>544</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th></th>\n",
    "        <th>2</th>\n",
    "        <td>P006</td>\n",
    "        <td>4</td>\n",
    "        <td>35000</td>\n",
    "        <td>2800</td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "k-GTrwmzT6AG",
    "outputId": "d68faa2e-b65e-43eb-e447-6f43a7febef4"
   },
   "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>Product_ID</th>\n",
       "      <th>Qty</th>\n",
       "      <th>Amount</th>\n",
       "      <th>Tax</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sales_No</th>\n",
       "      <th>Sales_Item_No</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\">A001</th>\n",
       "      <th>1</th>\n",
       "      <td>P001</td>\n",
       "      <td>1</td>\n",
       "      <td>7000</td>\n",
       "      <td>560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P002</td>\n",
       "      <td>2</td>\n",
       "      <td>7700</td>\n",
       "      <td>616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">A002</th>\n",
       "      <th>1</th>\n",
       "      <td>P003</td>\n",
       "      <td>1</td>\n",
       "      <td>6800</td>\n",
       "      <td>544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P006</td>\n",
       "      <td>4</td>\n",
       "      <td>35000</td>\n",
       "      <td>2800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       Product_ID  Qty  Amount   Tax\n",
       "Sales_No Sales_Item_No                              \n",
       "A001     1                   P001    1    7000   560\n",
       "         2                   P002    2    7700   616\n",
       "A002     1                   P003    1    6800   544\n",
       "         2                   P006    4   35000  2800"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.DataFrame({'Product_ID': ['P001', 'P002', 'P003', 'P006'], 'Qty': [1, 2, 1, 4], \\\n",
    "                      'Amount': [7000, 7700, 6800, 35000], 'Tax': [560, 616, 544, 2800]}, \\\n",
    "                     index=[['A001', 'A001', 'A002', 'A002'], [1, 2, 1, 2]])\n",
    "frame.index.names = ['Sales_No', 'Sales_Item_No']\n",
    "frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ugNnQqOldXd5"
   },
   "source": [
    "## 練習問題5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4un6VLXjdZxY"
   },
   "source": [
    "練習問題4で作ったデータフレームに対し、Sales_No=A002のAmountの合計を求めよ。\n",
    "\n",
    "ヒント. 和を表すデータフレームを作り、Sales_No=A002のAmountの要素を取り出す。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "rL0SojxKbL7M",
    "outputId": "f1a0a5d4-d7b1-402e-ba64-818c0c342293"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "41800"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.sum(level='Sales_No').loc['A002', 'Amount']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "PXRsftUwktrp"
   },
   "source": [
    "## 練習問題6"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jxKB2uZ3kvm4"
   },
   "source": [
    "以下のようなデータフレームemploymentを作り、\n",
    "\n",
    "<table>\n",
    "    <tr>\n",
    "        <th>year</th>\n",
    "        <th>H27</th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th>H22</th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>sector</th>\n",
    "        <th>pr</th>\n",
    "        <th>sec</th>\n",
    "        <th>ter</th>\n",
    "        <th>pr</th>\n",
    "        <th>sec</th>\n",
    "        <th>ter</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>city</th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Nagasaki</th>\n",
    "        <td>3658</td>\n",
    "        <td>36181</td>\n",
    "        <td>146548</td>\n",
    "        <td>4060</td>\n",
    "        <td>35833</td>\n",
    "        <td>149230</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Sasebo</th>\n",
    "        <td>4828</td>\n",
    "        <td>21498</td>\n",
    "        <td>86854</td>\n",
    "        <td>5180</td>\n",
    "        <td>22374</td>\n",
    "        <td>86683</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Shimabara</th>\n",
    "        <td>3214</td>\n",
    "        <td>4203</td>\n",
    "        <td>13869</td>\n",
    "        <td>3310</td>\n",
    "        <td>4321</td>\n",
    "        <td>13775</td>\n",
    "    </tr>\n",
    "</table>\n",
    "\n",
    "これを以下のようなデータフレームに変換せよ。\n",
    "\n",
    "<table>\n",
    "    <tr>\n",
    "        <th>sector</th>\n",
    "        <th></th>\n",
    "        <th>pr</th>\n",
    "        <th>sec</th>\n",
    "        <th>ter</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>city</th>\n",
    "        <th>year</th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Nagasaki</th>\n",
    "        <th>H22</th>\n",
    "        <td>4060</td>\n",
    "        <td>35833</td>\n",
    "        <td>149230</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Nagasaki</th>\n",
    "        <th>H27</th>\n",
    "        <td>3658</td>\n",
    "        <td>36181</td>\n",
    "        <td>146548</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Sasebo</th>\n",
    "        <th>H22</th>\n",
    "        <td>5180</td>\n",
    "        <td>22374</td>\n",
    "        <td>86683</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Sasebo</th>\n",
    "        <th>H27</th>\n",
    "        <td>4828</td>\n",
    "        <td>21498</td>\n",
    "        <td>86854</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Shimabara</th>\n",
    "        <th>H22</th>\n",
    "        <td>3310</td>\n",
    "        <td>4321</td>\n",
    "        <td>13775</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Shimabara</th>\n",
    "        <th>H27</th>\n",
    "        <td>3214</td>\n",
    "        <td>4203</td>\n",
    "        <td>13869</td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 266
    },
    "id": "WZd51Zo1cpJH",
    "outputId": "d86ed6df-1578-447e-f2c2-71ec55afbf5d"
   },
   "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>sector</th>\n",
       "      <th>pr</th>\n",
       "      <th>sec</th>\n",
       "      <th>ter</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Nagasaki</th>\n",
       "      <th>H22</th>\n",
       "      <td>4060</td>\n",
       "      <td>35833</td>\n",
       "      <td>149230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H27</th>\n",
       "      <td>3658</td>\n",
       "      <td>36181</td>\n",
       "      <td>146548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Sasebo</th>\n",
       "      <th>H22</th>\n",
       "      <td>5180</td>\n",
       "      <td>22374</td>\n",
       "      <td>86683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H27</th>\n",
       "      <td>4828</td>\n",
       "      <td>21498</td>\n",
       "      <td>86854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Shimabara</th>\n",
       "      <th>H22</th>\n",
       "      <td>3310</td>\n",
       "      <td>4321</td>\n",
       "      <td>13775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H27</th>\n",
       "      <td>3214</td>\n",
       "      <td>4203</td>\n",
       "      <td>13869</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "sector            pr    sec     ter\n",
       "city      year                     \n",
       "Nagasaki  H22   4060  35833  149230\n",
       "          H27   3658  36181  146548\n",
       "Sasebo    H22   5180  22374   86683\n",
       "          H27   4828  21498   86854\n",
       "Shimabara H22   3310   4321   13775\n",
       "          H27   3214   4203   13869"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employment = pd.DataFrame(np.array([3658, 36181, 146548, 4060, 35833, 149230, \\\n",
    "                                    4828, 21498, 86854, 5180, 22374, 86683, \\\n",
    "                                    3214, 4203, 13869, 3310, 4321, 13775]).reshape(3, 6), \\\n",
    "                          index=pd.Index(['Nagasaki', 'Sasebo', 'Shimabara'], name='city'), \\\n",
    "                          columns=pd.MultiIndex.from_arrays([['H27', 'H27', 'H27', 'H22', 'H22', 'H22'], \\\n",
    "                                                             ['pr', 'sec', 'ter', 'pr', 'sec', 'ter']], \\\n",
    "                                                            names=['year', 'sector']))\n",
    "#employment\n",
    "employment.stack(level='year')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "t9ikMQmMraJx"
   },
   "source": [
    "## 練習問題7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ha96X1QSrcXZ"
   },
   "source": [
    "以下のデータフレームuriageを作成し、商品ごとに売り上げた価格の合計を表すシリーズを求めよ。Priceは商品一つ当たりの価格、Quantityは売り上げた個数である。ただしfor文は使用しないこと。\n",
    "\n",
    "<table>\n",
    "    <tr>\n",
    "        <th></th>\n",
    "        <th>Goods</th>\n",
    "        <th>Price</th>\n",
    "        <th>Quantity</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Yamada</th>\n",
    "        <td>Printer</td>\n",
    "        <td>30000</td>\n",
    "        <td>5</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Suzuki</th>\n",
    "        <td>DigCam</td>\n",
    "        <td>25000</td>\n",
    "        <td>8</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Suzuki</th>\n",
    "        <td>Scanner</td>\n",
    "        <td>25000</td>\n",
    "        <td>8</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Kato</th>\n",
    "        <td>PC</td>\n",
    "        <td>11000</td>\n",
    "        <td>6</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Yamada</th>\n",
    "        <td>Printer</td>\n",
    "        <td>30000</td>\n",
    "        <td>3</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Yamada</th>\n",
    "        <td>PC</td>\n",
    "        <td>110000</td>\n",
    "        <td>3</td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "VNMjgFLEligy",
    "outputId": "b72a9328-176f-42ff-8f51-1eef1c763ab0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Goods\n",
       "DigCam     200000\n",
       "PC         990000\n",
       "Printer    240000\n",
       "Scanner     60000\n",
       "dtype: int64"
      ]
     },
     "execution_count": 75,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uriage = pd.DataFrame({'Goods': ['Printer', 'DigCam', 'Scanner', 'PC', 'Printer', 'PC'], \\\n",
    "                       'Price': [30000, 25000, 20000, 110000, 30000, 110000], \\\n",
    "                       'Quantity': [5, 8, 3, 6, 3, 3]},\n",
    "                      index=['Yamada', 'Suzuki', 'Suziki', 'Kato', 'Yamada', 'Yamada'])\n",
    "uriage['Price'].groupby(uriage['Goods']).mean() * uriage['Quantity'].groupby(uriage['Goods']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cmTf5pm2y8Ug"
   },
   "source": [
    "## 練習問題8"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "nGlbzHk6y-1v"
   },
   "source": [
    "練習問題6で作ったデータフレームemploymentに対し、各年度の就業者合計数を表した以下のようなデータフレームを作成せよ。\n",
    "\n",
    "<table>\n",
    "    <tr>\n",
    "        <th>year</th>\n",
    "        <th>H22</th>\n",
    "        <th>H27</th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>city</th>\n",
    "        <th></th>\n",
    "        <th></th>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Nagasaki</th>\n",
    "        <td>189123</td>\n",
    "        <td>186387</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Sasebo</th>\n",
    "        <td>114237</td>\n",
    "        <td>113180</td>\n",
    "    </tr>\n",
    "    <tr>\n",
    "        <th>Shimabara</th>\n",
    "        <td>21406</td>\n",
    "        <td>21286</td>\n",
    "    </tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 173
    },
    "id": "v6aQXkI5y96X",
    "outputId": "87a2a5d7-2833-4332-fd05-b5a941b5c03d"
   },
   "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>year</th>\n",
       "      <th>H22</th>\n",
       "      <th>H27</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>Nagasaki</th>\n",
       "      <td>189123</td>\n",
       "      <td>186387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sasebo</th>\n",
       "      <td>114237</td>\n",
       "      <td>113180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shimabara</th>\n",
       "      <td>21406</td>\n",
       "      <td>21286</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "year          H22     H27\n",
       "city                     \n",
       "Nagasaki   189123  186387\n",
       "Sasebo     114237  113180\n",
       "Shimabara   21406   21286"
      ]
     },
     "execution_count": 87,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "employment.groupby(level='year', axis=1).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MJYRPa4s1mGo"
   },
   "source": [
    "## 練習問題9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XfQh7CJl1oaf"
   },
   "source": [
    "練習問題7で作ったデータフレームuriageに対し、商品ごとにPriceとQuantityを示したデータフレームを求めよ。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "id": "xo0A7dui0OlJ",
    "outputId": "e7c0e119-1474-41c1-a56c-96964855bf5c"
   },
   "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>Price</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Goods</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>DigCam</th>\n",
       "      <td>25000</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PC</th>\n",
       "      <td>110000</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Printer</th>\n",
       "      <td>30000</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scanner</th>\n",
       "      <td>20000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Price  Quantity\n",
       "Goods                    \n",
       "DigCam    25000         8\n",
       "PC       110000         9\n",
       "Printer   30000         8\n",
       "Scanner   20000         3"
      ]
     },
     "execution_count": 88,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uriage.groupby('Goods').agg({'Price': 'mean', 'Quantity': 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "NWPq-XZu2GkP"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "class_12.ipynb",
   "provenance": []
  },
  "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}