D14_用 pandas 撰寫樞紐分析表.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "mathematical-tonight",
   "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>chinese_score</th>\n",
       "      <th>english_score</th>\n",
       "      <th>math_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>class</th>\n",
       "      <th>student_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">boy</th>\n",
       "      <th>1</th>\n",
       "      <th>1</th>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>2</th>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>3</th>\n",
       "      <td>55</td>\n",
       "      <td>43</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>69</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">girl</th>\n",
       "      <th>1</th>\n",
       "      <th>5</th>\n",
       "      <td>60</td>\n",
       "      <td>79</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>6</th>\n",
       "      <td>55</td>\n",
       "      <td>68</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>7</th>\n",
       "      <td>77</td>\n",
       "      <td>70</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>8</th>\n",
       "      <td>76</td>\n",
       "      <td>77</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>9</th>\n",
       "      <td>60</td>\n",
       "      <td>57</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>10</th>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">boy</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>11</th>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>23</td>\n",
       "      <td>60</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">girl</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">3</th>\n",
       "      <th>13</th>\n",
       "      <td>66</td>\n",
       "      <td>90</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>77</td>\n",
       "      <td>67</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       chinese_score  english_score  math_score\n",
       "sex  class student_id                                          \n",
       "boy  1     1                      70             80          50\n",
       "     2     2                      50             45          60\n",
       "     1     3                      55             43          98\n",
       "     2     4                      89             69          70\n",
       "girl 1     5                      60             79          56\n",
       "     2     6                      55             68          60\n",
       "     1     7                      77             70          45\n",
       "     2     8                      76             77          55\n",
       "     1     9                      60             57          25\n",
       "     3     10                     43             40          88\n",
       "boy  3     11                     45             60          25\n",
       "           12                     23             60          80\n",
       "girl 3     13                     66             90          20\n",
       "           14                     50             50          50\n",
       "           15                     77             67          89"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#作業目標：\n",
    "#熟悉索引與欄位設定\n",
    "#使用樞紐建立新資料\n",
    "\n",
    "#作業重點：\n",
    "#多維度索引、欄位是有順序性的須注意\n",
    "#使用樞紐建立資料需要注意參數\n",
    "#index：新資料的索引名稱\n",
    "#columns：新資料的欄位名稱\n",
    "#values：新資料的值名稱\n",
    "\n",
    "#題目：\n",
    "#運用下列分數資料重新建構資料，將\n",
    "#索引(index)依序改為sex、class、student_id，欄位依序改成chinese_score、english_score、math_score\n",
    "\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "score_df = pd.DataFrame([[1,50,80,70,'boy',1],[2,60,45,50,'boy',2],[3,98,43,55,'boy',1],[4,70,69,89,'boy',2],[5,56,79,60,'girl',1],[6,60,68,55,'girl',2],[7,45,70,77,'girl',1],[8,55,77,76,'girl',2],[9,25,57,60,'girl',1],[10,88,40,43,'girl',3],[11,25,60,45,'boy',3],[12,80,60,23,'boy',3],[13,20,90,66,'girl',3],[14,50,50,50,'girl',3],[15,89,67,77,'girl',3]],columns=['student_id','math_score','english_score','chinese_score','sex','class'])\n",
    "score_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "outstanding-conversation",
   "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>chinese_score</th>\n",
       "      <th>english_score</th>\n",
       "      <th>math_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>class</th>\n",
       "      <th>student_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">boy</th>\n",
       "      <th>1</th>\n",
       "      <th>1</th>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>2</th>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>3</th>\n",
       "      <td>55</td>\n",
       "      <td>43</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>69</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">girl</th>\n",
       "      <th>1</th>\n",
       "      <th>5</th>\n",
       "      <td>60</td>\n",
       "      <td>79</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>6</th>\n",
       "      <td>55</td>\n",
       "      <td>68</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>7</th>\n",
       "      <td>77</td>\n",
       "      <td>70</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>8</th>\n",
       "      <td>76</td>\n",
       "      <td>77</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>9</th>\n",
       "      <td>60</td>\n",
       "      <td>57</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>10</th>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">boy</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>11</th>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>23</td>\n",
       "      <td>60</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">girl</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">3</th>\n",
       "      <th>13</th>\n",
       "      <td>66</td>\n",
       "      <td>90</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>77</td>\n",
       "      <td>67</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       chinese_score  english_score  math_score\n",
       "sex  class student_id                                          \n",
       "boy  1     1                      70             80          50\n",
       "     2     2                      50             45          60\n",
       "     1     3                      55             43          98\n",
       "     2     4                      89             69          70\n",
       "girl 1     5                      60             79          56\n",
       "     2     6                      55             68          60\n",
       "     1     7                      77             70          45\n",
       "     2     8                      76             77          55\n",
       "     1     9                      60             57          25\n",
       "     3     10                     43             40          88\n",
       "boy  3     11                     45             60          25\n",
       "           12                     23             60          80\n",
       "girl 3     13                     66             90          20\n",
       "           14                     50             50          50\n",
       "           15                     77             67          89"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#將索引(index)依序改為sex、class、student_id，欄位依序改成chinese_score、english_score、math_score\n",
    "\n",
    "score_df.pivot(index=['sex','class','student_id'], columns=[],values=['chinese_score','english_score','math_score'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ordered-vaccine",
   "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>variable</th>\n",
       "      <th>chinese_score</th>\n",
       "      <th>english_score</th>\n",
       "      <th>math_score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th>class</th>\n",
       "      <th>student_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"6\" valign=\"top\">boy</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>1</th>\n",
       "      <td>70</td>\n",
       "      <td>80</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>55</td>\n",
       "      <td>43</td>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>2</th>\n",
       "      <td>50</td>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>89</td>\n",
       "      <td>69</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>11</th>\n",
       "      <td>45</td>\n",
       "      <td>60</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>23</td>\n",
       "      <td>60</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"9\" valign=\"top\">girl</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">1</th>\n",
       "      <th>5</th>\n",
       "      <td>60</td>\n",
       "      <td>79</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>77</td>\n",
       "      <td>70</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>60</td>\n",
       "      <td>57</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>6</th>\n",
       "      <td>55</td>\n",
       "      <td>68</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>76</td>\n",
       "      <td>77</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">3</th>\n",
       "      <th>10</th>\n",
       "      <td>43</td>\n",
       "      <td>40</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>66</td>\n",
       "      <td>90</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>77</td>\n",
       "      <td>67</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "variable               chinese_score  english_score  math_score\n",
       "sex  class student_id                                          \n",
       "boy  1     1                      70             80          50\n",
       "           3                      55             43          98\n",
       "     2     2                      50             45          60\n",
       "           4                      89             69          70\n",
       "     3     11                     45             60          25\n",
       "           12                     23             60          80\n",
       "girl 1     5                      60             79          56\n",
       "           7                      77             70          45\n",
       "           9                      60             57          25\n",
       "     2     6                      55             68          60\n",
       "           8                      76             77          55\n",
       "     3     10                     43             40          88\n",
       "           13                     66             90          20\n",
       "           14                     50             50          50\n",
       "           15                     77             67          89"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#將索引(index)依序改為sex、class、student_id，欄位依序改成chinese_score、english_score、math_score\n",
    "\n",
    "df = score_df.melt(id_vars=['sex','class','student_id'])\n",
    "df.pivot_table(index=['sex','class','student_id'],columns='variable',values='value')"
   ]
  }
 ],
 "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.9.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}