D15_pandas Split-Apply-Combine Strategy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "WpY62-EDcs8F"
   },
   "source": [
    "作業目標:<br>\n",
    "1. 熟悉分組計算"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pXYQs1_9a6He"
   },
   "source": [
    "作業重點: <br>\n",
    "1. 分組計算也可以加入apply自定義的函數"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vysasgqxcupS"
   },
   "source": [
    "題目 : 運用下列分數資料分析<br>\n",
    "1. 找出全年級各科成績最高分與最低分?<br>\n",
    "2. 找出數學班平均最高的班級?<br>\n",
    "3. 分析全校女生與男生國文平均差幾分?<br>\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'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 514
    },
    "executionInfo": {
     "elapsed": 1215,
     "status": "ok",
     "timestamp": 1608456627947,
     "user": {
      "displayName": "献竤黃",
      "photoUrl": "",
      "userId": "07529243043474362942"
     },
     "user_tz": -480
    },
    "id": "3WzHnm4zct7L",
    "outputId": "d36e7caa-72c8-40c5-e160-c3548a85af78"
   },
   "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>student_id</th>\n",
       "      <th>math_score</th>\n",
       "      <th>english_score</th>\n",
       "      <th>chinese_score</th>\n",
       "      <th>sex</th>\n",
       "      <th>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>80</td>\n",
       "      <td>70</td>\n",
       "      <td>boy</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>60</td>\n",
       "      <td>45</td>\n",
       "      <td>50</td>\n",
       "      <td>boy</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>98</td>\n",
       "      <td>43</td>\n",
       "      <td>55</td>\n",
       "      <td>boy</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>70</td>\n",
       "      <td>69</td>\n",
       "      <td>89</td>\n",
       "      <td>boy</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>56</td>\n",
       "      <td>79</td>\n",
       "      <td>60</td>\n",
       "      <td>girl</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>60</td>\n",
       "      <td>68</td>\n",
       "      <td>55</td>\n",
       "      <td>girl</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>45</td>\n",
       "      <td>70</td>\n",
       "      <td>77</td>\n",
       "      <td>girl</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>55</td>\n",
       "      <td>77</td>\n",
       "      <td>76</td>\n",
       "      <td>girl</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>25</td>\n",
       "      <td>57</td>\n",
       "      <td>60</td>\n",
       "      <td>girl</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>88</td>\n",
       "      <td>40</td>\n",
       "      <td>43</td>\n",
       "      <td>girl</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>25</td>\n",
       "      <td>60</td>\n",
       "      <td>45</td>\n",
       "      <td>boy</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>80</td>\n",
       "      <td>60</td>\n",
       "      <td>23</td>\n",
       "      <td>boy</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>20</td>\n",
       "      <td>90</td>\n",
       "      <td>66</td>\n",
       "      <td>girl</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>girl</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>89</td>\n",
       "      <td>67</td>\n",
       "      <td>77</td>\n",
       "      <td>girl</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    student_id  math_score  english_score  chinese_score   sex  class\n",
       "0            1          50             80             70   boy      1\n",
       "1            2          60             45             50   boy      2\n",
       "2            3          98             43             55   boy      1\n",
       "3            4          70             69             89   boy      2\n",
       "4            5          56             79             60  girl      1\n",
       "5            6          60             68             55  girl      2\n",
       "6            7          45             70             77  girl      1\n",
       "7            8          55             77             76  girl      2\n",
       "8            9          25             57             60  girl      1\n",
       "9           10          88             40             43  girl      3\n",
       "10          11          25             60             45   boy      3\n",
       "11          12          80             60             23   boy      3\n",
       "12          13          20             90             66  girl      3\n",
       "13          14          50             50             50  girl      3\n",
       "14          15          89             67             77  girl      3"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "score_df = pd.DataFrame([[1,50,80,70,'boy',1], \n",
    "              [2,60,45,50,'boy',2],\n",
    "              [3,98,43,55,'boy',1],\n",
    "              [4,70,69,89,'boy',2],\n",
    "              [5,56,79,60,'girl',1],\n",
    "              [6,60,68,55,'girl',2],\n",
    "              [7,45,70,77,'girl',1],\n",
    "              [8,55,77,76,'girl',2],\n",
    "              [9,25,57,60,'girl',1],\n",
    "              [10,88,40,43,'girl',3],\n",
    "              [11,25,60,45,'boy',3],\n",
    "              [12,80,60,23,'boy',3],\n",
    "              [13,20,90,66,'girl',3],\n",
    "              [14,50,50,50,'girl',3],\n",
    "              [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": null,
   "metadata": {
    "id": "Zfs1uSNPfNBL"
   },
   "outputs": [],
   "source": [
    "#1.找出全年級各科成績最高分與最低分?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Math_score\n",
      "max    98\n",
      "min    20\n",
      "Name: math_score, dtype: int64\n",
      "\n",
      "English_score\n",
      "max    90\n",
      "min    40\n",
      "Name: english_score, dtype: int64\n",
      "\n",
      "Chinese_score\n",
      "max    89\n",
      "min    23\n",
      "Name: chinese_score, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print('Math_score')\n",
    "print(score_df['math_score'].agg([max, min]),end='\\n\\n')\n",
    "print('English_score')\n",
    "print(score_df['english_score'].agg([max, min]),end='\\n\\n')\n",
    "print('Chinese_score')\n",
    "print(score_df['chinese_score'].agg([max, min]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "iZdLLEzYfPnW"
   },
   "outputs": [],
   "source": [
    "#2.找出數學班平均最高的班級?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score_df.groupby('class')['math_score'].mean().idxmax()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "CWxb-2TqfbNK"
   },
   "outputs": [],
   "source": [
    "#3.分析全校女生與男生國文平均差幾分?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "id": "aBC5K-q6jFvb"
   },
   "outputs": [],
   "source": [
    "sex_diff = score_df.groupby('sex')['chinese_score'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "boy     55.333333\n",
       "girl    62.666667\n",
       "Name: chinese_score, dtype: float64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sex_diff"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7.333333333333329"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sex_diff.iloc[1] - sex_diff.iloc[0]"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "作業題目.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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}