ch11 test01.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "81224142",
   "metadata": {},
   "source": [
    "# 실접 종합 문제1\n",
    "\n",
    "## 01 시나리오\n",
    "\n",
    "서울시 소재의 DS 운수에서는 정기적으로 회사 경영진과 버스기사 간 연봉과 근무 처우 관련하여 협상을 한다. 올해부터 데이터 분석을 본격적으로 도입하기로 하였다. 그리하여 가가자의 의견을 조율함에 있어 데이터 분석을 기반으로 보다 객관적인 의사결정을 하고자 한다.\n",
    "\n",
    "## 02 데이터 설명\n",
    "\n",
    "서울시 2019년 승차정보 - Seoul_Bus_2019.csv  \n",
    "서울시 버스 정보 - Seoul_Bus_info.csv\n",
    "\n",
    "## 03 문제\n",
    "\n",
    "### 1번\n",
    "\n",
    "지선, 간선 버스의 경우 노선당 년 수익이 10억 이하인 경우 배차간격 조정이나 노선 변경 등 수익구조 개선이 필요하다고 한다. 승객 1명당 기대 수익이 천 원이라고 했을 때 몇 개의 버스 노선이 대상인가? (정답 예시: 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6436f50e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from scipy.stats import ttest_ind\n",
    "from sklearn.cluster import AgglomerativeClustering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5c8bc0e6",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H15</th>\n",
       "      <th>H16</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000184</td>\n",
       "      <td>석계역2번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>33</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000327</td>\n",
       "      <td>한성여객종점</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>131</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>15</td>\n",
       "      <td>21</td>\n",
       "      <td>46</td>\n",
       "      <td>42</td>\n",
       "      <td>66</td>\n",
       "      <td>57</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000286</td>\n",
       "      <td>노원평생학습관</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>28</td>\n",
       "      <td>34</td>\n",
       "      <td>34</td>\n",
       "      <td>48</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>21</td>\n",
       "      <td>25</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000273</td>\n",
       "      <td>서울시립북서울미술관</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>83</td>\n",
       "      <td>101</td>\n",
       "      <td>132</td>\n",
       "      <td>105</td>\n",
       "      <td>79</td>\n",
       "      <td>65</td>\n",
       "      <td>43</td>\n",
       "      <td>91</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000272</td>\n",
       "      <td>중계역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>61</td>\n",
       "      <td>79</td>\n",
       "      <td>57</td>\n",
       "      <td>68</td>\n",
       "      <td>73</td>\n",
       "      <td>48</td>\n",
       "      <td>47</td>\n",
       "      <td>45</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year_Month   Line_ID Line_No             Line_Name  Station_ID  \\\n",
       "0      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000184   \n",
       "1      201901  11110001     100        100번(하계동~용산구청)   110000327   \n",
       "2      201901  11110001     100        100번(하계동~용산구청)   110000286   \n",
       "3      201901  11110001     100        100번(하계동~용산구청)   110000273   \n",
       "4      201901  11110001     100        100번(하계동~용산구청)   110000272   \n",
       "\n",
       "  Station_Name  H01  H02  H03  H04  ...  H15  H16  H17  H18  H19  H20  H21  \\\n",
       "0      석계역2번출구    0    0   25   33  ...    0    0    0    0    0    0    0   \n",
       "1       한성여객종점    0    0    0  131  ...   13   15   21   46   42   66   57   \n",
       "2      노원평생학습관    0    0    0    0  ...   28   34   34   48   21   24   21   \n",
       "3   서울시립북서울미술관    0    0    0    0  ...   83  101  132  105   79   65   43   \n",
       "4          중계역    0    0    0    0  ...   61   79   57   68   73   48   47   \n",
       "\n",
       "   H22  H23  H24  \n",
       "0    0    0    0  \n",
       "1    9    6    0  \n",
       "2   25    8    0  \n",
       "3   91   63    0  \n",
       "4   45   16    0  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"실습파일/Seoul_Bus_2019.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c64c48c0",
   "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>Bus_no</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>102</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>104</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Bus_no type\n",
       "0    N13   심야\n",
       "1    100   간선\n",
       "2    101   간선\n",
       "3    102   간선\n",
       "4    104   간선"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_info = pd.read_csv(\"실습파일/Seoul_Bus_info.csv\")\n",
    "df_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "eba7ad46",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>Bus_no</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000184</td>\n",
       "      <td>석계역2번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>33</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000379</td>\n",
       "      <td>석계역1번출구.B</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>67</td>\n",
       "      <td>36</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>107000055</td>\n",
       "      <td>석관동주민센터</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>101000295</td>\n",
       "      <td>약수역10번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>123000010</td>\n",
       "      <td>가락시장.가락시장역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year_Month   Line_ID Line_No             Line_Name  Station_ID  \\\n",
       "0      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000184   \n",
       "1      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000379   \n",
       "2      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   107000055   \n",
       "3      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   101000295   \n",
       "4      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   123000010   \n",
       "\n",
       "  Station_Name  H01  H02  H03  H04  ...  H17  H18  H19  H20  H21  H22  H23  \\\n",
       "0      석계역2번출구    0    0   25   33  ...    0    0    0    0    0    0    0   \n",
       "1    석계역1번출구.B    0    0   67   36  ...    0    0    0    0    0    0    0   \n",
       "2      석관동주민센터    0    0   20    8  ...    0    0    0    0    0    0    0   \n",
       "3     약수역10번출구    0    0    0    7  ...    0    0    0    0    0    0    0   \n",
       "4   가락시장.가락시장역    0    0    0   27  ...    0    0    0    0    0    0    0   \n",
       "\n",
       "   H24  Bus_no  type  \n",
       "0    0     N13    심야  \n",
       "1    0     N13    심야  \n",
       "2    0     N13    심야  \n",
       "3    0     N13    심야  \n",
       "4    0     N13    심야  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_join = df.merge(df_info, left_on = \"Line_No\", right_on = \"Bus_no\")\n",
    "df_join.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "40accaf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_q1 = df_join.copy()  # 깊은 복사 (실제로 복사함, 주소만 복사하는 것이 아님)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "855e2363",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>Bus_no</th>\n",
       "      <th>type</th>\n",
       "      <th>month_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000184</td>\n",
       "      <td>석계역2번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>33</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000379</td>\n",
       "      <td>석계역1번출구.B</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>67</td>\n",
       "      <td>36</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "      <td>103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>107000055</td>\n",
       "      <td>석관동주민센터</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>101000295</td>\n",
       "      <td>약수역10번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>123000010</td>\n",
       "      <td>가락시장.가락시장역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year_Month   Line_ID Line_No             Line_Name  Station_ID  \\\n",
       "0      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000184   \n",
       "1      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000379   \n",
       "2      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   107000055   \n",
       "3      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   101000295   \n",
       "4      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   123000010   \n",
       "\n",
       "  Station_Name  H01  H02  H03  H04  ...  H18  H19  H20  H21  H22  H23  H24  \\\n",
       "0      석계역2번출구    0    0   25   33  ...    0    0    0    0    0    0    0   \n",
       "1    석계역1번출구.B    0    0   67   36  ...    0    0    0    0    0    0    0   \n",
       "2      석관동주민센터    0    0   20    8  ...    0    0    0    0    0    0    0   \n",
       "3     약수역10번출구    0    0    0    7  ...    0    0    0    0    0    0    0   \n",
       "4   가락시장.가락시장역    0    0    0   27  ...    0    0    0    0    0    0    0   \n",
       "\n",
       "   Bus_no  type  month_total  \n",
       "0     N13    심야           58  \n",
       "1     N13    심야          103  \n",
       "2     N13    심야           28  \n",
       "3     N13    심야           10  \n",
       "4     N13    심야           67  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q1[\"month_total\"] = df_q1.loc[:, \"H01\":\"H24\"].sum(axis = 1)\n",
    "df_q1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "79cbb63c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['심야', '간선', '순환', '지선', '마을', '맞춤', '공항', '광역'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q1[\"type\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "1865ec86",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>Bus_no</th>\n",
       "      <th>type</th>\n",
       "      <th>month_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3540</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000327</td>\n",
       "      <td>한성여객종점</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>131</td>\n",
       "      <td>...</td>\n",
       "      <td>46</td>\n",
       "      <td>42</td>\n",
       "      <td>66</td>\n",
       "      <td>57</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "      <td>683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3541</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000286</td>\n",
       "      <td>노원평생학습관</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>48</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>21</td>\n",
       "      <td>25</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "      <td>404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3542</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000273</td>\n",
       "      <td>서울시립북서울미술관</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>105</td>\n",
       "      <td>79</td>\n",
       "      <td>65</td>\n",
       "      <td>43</td>\n",
       "      <td>91</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "      <td>1391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3543</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000272</td>\n",
       "      <td>중계역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>68</td>\n",
       "      <td>73</td>\n",
       "      <td>48</td>\n",
       "      <td>47</td>\n",
       "      <td>45</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "      <td>1713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3544</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000275</td>\n",
       "      <td>중계역2번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>300</td>\n",
       "      <td>348</td>\n",
       "      <td>324</td>\n",
       "      <td>282</td>\n",
       "      <td>214</td>\n",
       "      <td>103</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "      <td>5010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 33 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year_Month   Line_ID Line_No       Line_Name  Station_ID Station_Name  \\\n",
       "3540      201901  11110001     100  100번(하계동~용산구청)   110000327       한성여객종점   \n",
       "3541      201901  11110001     100  100번(하계동~용산구청)   110000286      노원평생학습관   \n",
       "3542      201901  11110001     100  100번(하계동~용산구청)   110000273   서울시립북서울미술관   \n",
       "3543      201901  11110001     100  100번(하계동~용산구청)   110000272          중계역   \n",
       "3544      201901  11110001     100  100번(하계동~용산구청)   110000275      중계역2번출구   \n",
       "\n",
       "      H01  H02  H03  H04  ...  H18  H19  H20  H21  H22  H23  H24  Bus_no  \\\n",
       "3540    0    0    0  131  ...   46   42   66   57    9    6    0     100   \n",
       "3541    0    0    0    0  ...   48   21   24   21   25    8    0     100   \n",
       "3542    0    0    0    0  ...  105   79   65   43   91   63    0     100   \n",
       "3543    0    0    0    0  ...   68   73   48   47   45   16    0     100   \n",
       "3544    0    0    0    0  ...  300  348  324  282  214  103    0     100   \n",
       "\n",
       "      type  month_total  \n",
       "3540    간선          683  \n",
       "3541    간선          404  \n",
       "3542    간선         1391  \n",
       "3543    간선         1713  \n",
       "3544    간선         5010  \n",
       "\n",
       "[5 rows x 33 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q1 = df_q1.loc[df_q1[\"type\"].isin([\"지선\", \"간선\"]), ]\n",
    "df_q1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "38448afd",
   "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>Line_No</th>\n",
       "      <th>month_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>6597898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>6685417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1014</td>\n",
       "      <td>3036623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1017</td>\n",
       "      <td>1868486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>102</td>\n",
       "      <td>5028570</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Line_No  month_total\n",
       "0     100      6597898\n",
       "1     101      6685417\n",
       "2    1014      3036623\n",
       "3    1017      1868486\n",
       "4     102      5028570"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q1_agg = df_q1.groupby(\"Line_No\")[\"month_total\"].sum().reset_index()\n",
    "df_q1_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c0ca0a0a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000000.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "1000000000 / 1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "2d775572",
   "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>Line_No</th>\n",
       "      <th>month_total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1113</td>\n",
       "      <td>770433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1114</td>\n",
       "      <td>646939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1115</td>\n",
       "      <td>829236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1116</td>\n",
       "      <td>638926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1120</td>\n",
       "      <td>987411</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Line_No  month_total\n",
       "16    1113       770433\n",
       "17    1114       646939\n",
       "18    1115       829236\n",
       "19    1116       638926\n",
       "21    1120       987411"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q1_agg_sub = df_q1_agg.loc[df_q1_agg[\"month_total\"] < 1000000, ]\n",
    "df_q1_agg_sub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8f1c6a3d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_q1_agg_sub)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7177a34d",
   "metadata": {},
   "source": [
    "### 2번\n",
    "간선 버스노선 버스기사들은 간선 버스노선이 지선 버스노선 대비 정규장 개수가 많아 버스 기사 확충 또는 배차 간격 조정을 사측에 요구하고 있다. 경영지원팀은 요구를 수용하기 위해 지선과 간선 노선의 정규장 개수를 파악하고 이 차이가 통계적으로 유의한지 확인해보려 한다. 간선 버스노선의 평균 정규장 개수와 지선 버스노선의 평균 정류장 개수의 평균을 적절한 검정을 통해 그 차이를 비교하고 그 검정통계량의 절대값을 소수점 둘째 자리까지 반올림하여 기술하시오. (정답 예시: 1.23)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "578a62b3",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>Bus_no</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000184</td>\n",
       "      <td>석계역2번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>33</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000379</td>\n",
       "      <td>석계역1번출구.B</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>67</td>\n",
       "      <td>36</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>107000055</td>\n",
       "      <td>석관동주민센터</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>101000295</td>\n",
       "      <td>약수역10번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>123000010</td>\n",
       "      <td>가락시장.가락시장역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year_Month   Line_ID Line_No             Line_Name  Station_ID  \\\n",
       "0      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000184   \n",
       "1      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000379   \n",
       "2      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   107000055   \n",
       "3      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   101000295   \n",
       "4      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   123000010   \n",
       "\n",
       "  Station_Name  H01  H02  H03  H04  ...  H17  H18  H19  H20  H21  H22  H23  \\\n",
       "0      석계역2번출구    0    0   25   33  ...    0    0    0    0    0    0    0   \n",
       "1    석계역1번출구.B    0    0   67   36  ...    0    0    0    0    0    0    0   \n",
       "2      석관동주민센터    0    0   20    8  ...    0    0    0    0    0    0    0   \n",
       "3     약수역10번출구    0    0    0    7  ...    0    0    0    0    0    0    0   \n",
       "4   가락시장.가락시장역    0    0    0   27  ...    0    0    0    0    0    0    0   \n",
       "\n",
       "   H24  Bus_no  type  \n",
       "0    0     N13    심야  \n",
       "1    0     N13    심야  \n",
       "2    0     N13    심야  \n",
       "3    0     N13    심야  \n",
       "4    0     N13    심야  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q2 = df_join.copy()\n",
    "df_q2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "c90127d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_q2_gan = df_q2.loc[df_q2[\"type\"] == \"간선\", ]\n",
    "df_q2_gee = df_q2.loc[df_q2[\"type\"] == \"지선\", ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "b7c738e0",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>Bus_no</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3540</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000327</td>\n",
       "      <td>한성여객종점</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>131</td>\n",
       "      <td>...</td>\n",
       "      <td>21</td>\n",
       "      <td>46</td>\n",
       "      <td>42</td>\n",
       "      <td>66</td>\n",
       "      <td>57</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3541</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000286</td>\n",
       "      <td>노원평생학습관</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>34</td>\n",
       "      <td>48</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>21</td>\n",
       "      <td>25</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3542</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000273</td>\n",
       "      <td>서울시립북서울미술관</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>132</td>\n",
       "      <td>105</td>\n",
       "      <td>79</td>\n",
       "      <td>65</td>\n",
       "      <td>43</td>\n",
       "      <td>91</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3543</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000272</td>\n",
       "      <td>중계역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>57</td>\n",
       "      <td>68</td>\n",
       "      <td>73</td>\n",
       "      <td>48</td>\n",
       "      <td>47</td>\n",
       "      <td>45</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3544</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110001</td>\n",
       "      <td>100</td>\n",
       "      <td>100번(하계동~용산구청)</td>\n",
       "      <td>110000275</td>\n",
       "      <td>중계역2번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>227</td>\n",
       "      <td>300</td>\n",
       "      <td>348</td>\n",
       "      <td>324</td>\n",
       "      <td>282</td>\n",
       "      <td>214</td>\n",
       "      <td>103</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>간선</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year_Month   Line_ID Line_No       Line_Name  Station_ID Station_Name  \\\n",
       "3540      201901  11110001     100  100번(하계동~용산구청)   110000327       한성여객종점   \n",
       "3541      201901  11110001     100  100번(하계동~용산구청)   110000286      노원평생학습관   \n",
       "3542      201901  11110001     100  100번(하계동~용산구청)   110000273   서울시립북서울미술관   \n",
       "3543      201901  11110001     100  100번(하계동~용산구청)   110000272          중계역   \n",
       "3544      201901  11110001     100  100번(하계동~용산구청)   110000275      중계역2번출구   \n",
       "\n",
       "      H01  H02  H03  H04  ...  H17  H18  H19  H20  H21  H22  H23  H24  Bus_no  \\\n",
       "3540    0    0    0  131  ...   21   46   42   66   57    9    6    0     100   \n",
       "3541    0    0    0    0  ...   34   48   21   24   21   25    8    0     100   \n",
       "3542    0    0    0    0  ...  132  105   79   65   43   91   63    0     100   \n",
       "3543    0    0    0    0  ...   57   68   73   48   47   45   16    0     100   \n",
       "3544    0    0    0    0  ...  227  300  348  324  282  214  103    0     100   \n",
       "\n",
       "      type  \n",
       "3540    간선  \n",
       "3541    간선  \n",
       "3542    간선  \n",
       "3543    간선  \n",
       "3544    간선  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q2_gan.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "b0b474b3",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>Bus_no</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>79153</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>990071180</td>\n",
       "      <td>북부운수차고지(가상)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>112</td>\n",
       "      <td>119</td>\n",
       "      <td>174</td>\n",
       "      <td>155</td>\n",
       "      <td>83</td>\n",
       "      <td>59</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79154</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>106000089</td>\n",
       "      <td>방약국앞</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>111</td>\n",
       "      <td>166</td>\n",
       "      <td>176</td>\n",
       "      <td>174</td>\n",
       "      <td>82</td>\n",
       "      <td>89</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79155</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>106000087</td>\n",
       "      <td>면남초등학교</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>136</td>\n",
       "      <td>102</td>\n",
       "      <td>192</td>\n",
       "      <td>149</td>\n",
       "      <td>61</td>\n",
       "      <td>112</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79156</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>106000084</td>\n",
       "      <td>지하철7호선사가정역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>159</td>\n",
       "      <td>133</td>\n",
       "      <td>258</td>\n",
       "      <td>161</td>\n",
       "      <td>114</td>\n",
       "      <td>123</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79157</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>106000082</td>\n",
       "      <td>면목우체국.녹색병원</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>208</td>\n",
       "      <td>219</td>\n",
       "      <td>364</td>\n",
       "      <td>354</td>\n",
       "      <td>246</td>\n",
       "      <td>214</td>\n",
       "      <td>159</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Year_Month   Line_ID Line_No        Line_Name  Station_ID Station_Name  \\\n",
       "79153      201901  11110077    2230  2230번(면목동~경동시장)   990071180  북부운수차고지(가상)   \n",
       "79154      201901  11110077    2230  2230번(면목동~경동시장)   106000089         방약국앞   \n",
       "79155      201901  11110077    2230  2230번(면목동~경동시장)   106000087       면남초등학교   \n",
       "79156      201901  11110077    2230  2230번(면목동~경동시장)   106000084   지하철7호선사가정역   \n",
       "79157      201901  11110077    2230  2230번(면목동~경동시장)   106000082   면목우체국.녹색병원   \n",
       "\n",
       "       H01  H02  H03  H04  ...  H17  H18  H19  H20  H21  H22  H23  H24  \\\n",
       "79153    0    0    0    0  ...  112  119  174  155   83   59   31    0   \n",
       "79154    0    0    0    0  ...  111  166  176  174   82   89   87    0   \n",
       "79155    0    0    0    0  ...  136  102  192  149   61  112   84    0   \n",
       "79156    0    0    0    0  ...  159  133  258  161  114  123   69    0   \n",
       "79157    0    0    0    0  ...  208  219  364  354  246  214  159    0   \n",
       "\n",
       "       Bus_no  type  \n",
       "79153    2230    지선  \n",
       "79154    2230    지선  \n",
       "79155    2230    지선  \n",
       "79156    2230    지선  \n",
       "79157    2230    지선  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q2_gee.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5e89ab02",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_q2_gan = df_q2_gan[[\"Line_No\", \"Station_ID\"]].drop_duplicates()\n",
    "df_q2_gee = df_q2_gee[[\"Line_No\", \"Station_ID\"]].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "d7afea26",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12594"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_q2_gan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "21a559cc",
   "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>Line_No</th>\n",
       "      <th>Station_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>104</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Line_No  Station_ID\n",
       "0     100         121\n",
       "1     101          83\n",
       "2     102          69\n",
       "3     103          71\n",
       "4     104          67"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q2_gan_cnt = df_q2_gan.groupby(\"Line_No\")[\"Station_ID\"].nunique().reset_index()\n",
    "df_q2_gee_cnt = df_q2_gee.groupby(\"Line_No\")[\"Station_ID\"].nunique().reset_index()\n",
    "df_q2_gan_cnt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "af09d769",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.5"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stat, p = ttest_ind(df_q2_gan_cnt[\"Station_ID\"],\n",
    "                    df_q2_gee_cnt[\"Station_ID\"])\n",
    "round(abs(stat), 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8b958c4",
   "metadata": {},
   "source": [
    "### 3번\n",
    "\n",
    "출퇴근 시간 배차간격 조정을 위해 우선적으로 각 정류소별 시간대별 출근 시간 승차 패턴을 파악하고자 한다. 지선 버스 노선을 대상으로 각 정규장별 승차인원을 계층적 군집분석을 활용하여 6개의 군집으로 분할하였을 때 출근시간대에 승차 인원이 가장 많은 정류소 군집의 번호는 몇 번인가? (정답 예시: 2)  \n",
    "\n",
    "1) 출근 시간대는 오전 7시부터 9시까지로 정의  \n",
    "2) 군집분석 시 자료는 Min-Max 정규화 실시  \n",
    "3) 거리 계산은 유클리디안 거리로 하고 유사도는 Ward.D 방법을 사용  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "bf9cfdcc",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>Bus_no</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000184</td>\n",
       "      <td>석계역2번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25</td>\n",
       "      <td>33</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>110000379</td>\n",
       "      <td>석계역1번출구.B</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>67</td>\n",
       "      <td>36</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>107000055</td>\n",
       "      <td>석관동주민센터</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>101000295</td>\n",
       "      <td>약수역10번출구</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110364</td>\n",
       "      <td>N13</td>\n",
       "      <td>N13번(송파공영차고지~상계동차고지)</td>\n",
       "      <td>123000010</td>\n",
       "      <td>가락시장.가락시장역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N13</td>\n",
       "      <td>심야</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year_Month   Line_ID Line_No             Line_Name  Station_ID  \\\n",
       "0      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000184   \n",
       "1      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   110000379   \n",
       "2      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   107000055   \n",
       "3      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   101000295   \n",
       "4      201901  11110364     N13  N13번(송파공영차고지~상계동차고지)   123000010   \n",
       "\n",
       "  Station_Name  H01  H02  H03  H04  ...  H17  H18  H19  H20  H21  H22  H23  \\\n",
       "0      석계역2번출구    0    0   25   33  ...    0    0    0    0    0    0    0   \n",
       "1    석계역1번출구.B    0    0   67   36  ...    0    0    0    0    0    0    0   \n",
       "2      석관동주민센터    0    0   20    8  ...    0    0    0    0    0    0    0   \n",
       "3     약수역10번출구    0    0    0    7  ...    0    0    0    0    0    0    0   \n",
       "4   가락시장.가락시장역    0    0    0   27  ...    0    0    0    0    0    0    0   \n",
       "\n",
       "   H24  Bus_no  type  \n",
       "0    0     N13    심야  \n",
       "1    0     N13    심야  \n",
       "2    0     N13    심야  \n",
       "3    0     N13    심야  \n",
       "4    0     N13    심야  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3 = df_join.copy()\n",
    "df_q3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "2f597f42",
   "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>Year_Month</th>\n",
       "      <th>Line_ID</th>\n",
       "      <th>Line_No</th>\n",
       "      <th>Line_Name</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>Station_Name</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>...</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>Bus_no</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>79153</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>990071180</td>\n",
       "      <td>북부운수차고지(가상)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>112</td>\n",
       "      <td>119</td>\n",
       "      <td>174</td>\n",
       "      <td>155</td>\n",
       "      <td>83</td>\n",
       "      <td>59</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79154</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>106000089</td>\n",
       "      <td>방약국앞</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>111</td>\n",
       "      <td>166</td>\n",
       "      <td>176</td>\n",
       "      <td>174</td>\n",
       "      <td>82</td>\n",
       "      <td>89</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79155</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>106000087</td>\n",
       "      <td>면남초등학교</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>136</td>\n",
       "      <td>102</td>\n",
       "      <td>192</td>\n",
       "      <td>149</td>\n",
       "      <td>61</td>\n",
       "      <td>112</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79156</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>106000084</td>\n",
       "      <td>지하철7호선사가정역</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>159</td>\n",
       "      <td>133</td>\n",
       "      <td>258</td>\n",
       "      <td>161</td>\n",
       "      <td>114</td>\n",
       "      <td>123</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79157</th>\n",
       "      <td>201901</td>\n",
       "      <td>11110077</td>\n",
       "      <td>2230</td>\n",
       "      <td>2230번(면목동~경동시장)</td>\n",
       "      <td>106000082</td>\n",
       "      <td>면목우체국.녹색병원</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>208</td>\n",
       "      <td>219</td>\n",
       "      <td>364</td>\n",
       "      <td>354</td>\n",
       "      <td>246</td>\n",
       "      <td>214</td>\n",
       "      <td>159</td>\n",
       "      <td>0</td>\n",
       "      <td>2230</td>\n",
       "      <td>지선</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Year_Month   Line_ID Line_No        Line_Name  Station_ID Station_Name  \\\n",
       "79153      201901  11110077    2230  2230번(면목동~경동시장)   990071180  북부운수차고지(가상)   \n",
       "79154      201901  11110077    2230  2230번(면목동~경동시장)   106000089         방약국앞   \n",
       "79155      201901  11110077    2230  2230번(면목동~경동시장)   106000087       면남초등학교   \n",
       "79156      201901  11110077    2230  2230번(면목동~경동시장)   106000084   지하철7호선사가정역   \n",
       "79157      201901  11110077    2230  2230번(면목동~경동시장)   106000082   면목우체국.녹색병원   \n",
       "\n",
       "       H01  H02  H03  H04  ...  H17  H18  H19  H20  H21  H22  H23  H24  \\\n",
       "79153    0    0    0    0  ...  112  119  174  155   83   59   31    0   \n",
       "79154    0    0    0    0  ...  111  166  176  174   82   89   87    0   \n",
       "79155    0    0    0    0  ...  136  102  192  149   61  112   84    0   \n",
       "79156    0    0    0    0  ...  159  133  258  161  114  123   69    0   \n",
       "79157    0    0    0    0  ...  208  219  364  354  246  214  159    0   \n",
       "\n",
       "       Bus_no  type  \n",
       "79153    2230    지선  \n",
       "79154    2230    지선  \n",
       "79155    2230    지선  \n",
       "79156    2230    지선  \n",
       "79157    2230    지선  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3 = df_q3.loc[df_q3[\"type\"] == \"지선\", ]\n",
    "df_q3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "fcc61ae4",
   "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>Year_Month</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>H05</th>\n",
       "      <th>H06</th>\n",
       "      <th>H07</th>\n",
       "      <th>H08</th>\n",
       "      <th>...</th>\n",
       "      <th>H15</th>\n",
       "      <th>H16</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>79153</th>\n",
       "      <td>201901</td>\n",
       "      <td>990071180</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>92</td>\n",
       "      <td>36</td>\n",
       "      <td>41</td>\n",
       "      <td>...</td>\n",
       "      <td>52</td>\n",
       "      <td>108</td>\n",
       "      <td>112</td>\n",
       "      <td>119</td>\n",
       "      <td>174</td>\n",
       "      <td>155</td>\n",
       "      <td>83</td>\n",
       "      <td>59</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79154</th>\n",
       "      <td>201901</td>\n",
       "      <td>106000089</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>76</td>\n",
       "      <td>40</td>\n",
       "      <td>49</td>\n",
       "      <td>...</td>\n",
       "      <td>122</td>\n",
       "      <td>149</td>\n",
       "      <td>111</td>\n",
       "      <td>166</td>\n",
       "      <td>176</td>\n",
       "      <td>174</td>\n",
       "      <td>82</td>\n",
       "      <td>89</td>\n",
       "      <td>87</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79155</th>\n",
       "      <td>201901</td>\n",
       "      <td>106000087</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>43</td>\n",
       "      <td>23</td>\n",
       "      <td>26</td>\n",
       "      <td>...</td>\n",
       "      <td>122</td>\n",
       "      <td>108</td>\n",
       "      <td>136</td>\n",
       "      <td>102</td>\n",
       "      <td>192</td>\n",
       "      <td>149</td>\n",
       "      <td>61</td>\n",
       "      <td>112</td>\n",
       "      <td>84</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79156</th>\n",
       "      <td>201901</td>\n",
       "      <td>106000084</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>52</td>\n",
       "      <td>48</td>\n",
       "      <td>35</td>\n",
       "      <td>33</td>\n",
       "      <td>...</td>\n",
       "      <td>133</td>\n",
       "      <td>162</td>\n",
       "      <td>159</td>\n",
       "      <td>133</td>\n",
       "      <td>258</td>\n",
       "      <td>161</td>\n",
       "      <td>114</td>\n",
       "      <td>123</td>\n",
       "      <td>69</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79157</th>\n",
       "      <td>201901</td>\n",
       "      <td>106000082</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33</td>\n",
       "      <td>96</td>\n",
       "      <td>45</td>\n",
       "      <td>41</td>\n",
       "      <td>...</td>\n",
       "      <td>203</td>\n",
       "      <td>200</td>\n",
       "      <td>208</td>\n",
       "      <td>219</td>\n",
       "      <td>364</td>\n",
       "      <td>354</td>\n",
       "      <td>246</td>\n",
       "      <td>214</td>\n",
       "      <td>159</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Year_Month  Station_ID  H01  H02  H03  H04  H05  H06  H07  H08  ...  \\\n",
       "79153      201901   990071180    0    0    0    0   29   92   36   41  ...   \n",
       "79154      201901   106000089    0    0    0    0   27   76   40   49  ...   \n",
       "79155      201901   106000087    0    0    0    0   11   43   23   26  ...   \n",
       "79156      201901   106000084    0    0    0    0   52   48   35   33  ...   \n",
       "79157      201901   106000082    0    0    0    0   33   96   45   41  ...   \n",
       "\n",
       "       H15  H16  H17  H18  H19  H20  H21  H22  H23  H24  \n",
       "79153   52  108  112  119  174  155   83   59   31    0  \n",
       "79154  122  149  111  166  176  174   82   89   87    0  \n",
       "79155  122  108  136  102  192  149   61  112   84    0  \n",
       "79156  133  162  159  133  258  161  114  123   69    0  \n",
       "79157  203  200  208  219  364  354  246  214  159    0  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3 = pd.concat([df_q3.loc[:, [\"Year_Month\", \"Station_ID\"]],\n",
    "                  df_q3.loc[:, \"H01\": \"H24\"]],\n",
    "                 axis = 1)\n",
    "df_q3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "045b67fb",
   "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>Station_ID</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>H05</th>\n",
       "      <th>H06</th>\n",
       "      <th>H07</th>\n",
       "      <th>H08</th>\n",
       "      <th>H09</th>\n",
       "      <th>...</th>\n",
       "      <th>H15</th>\n",
       "      <th>H16</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100000006</td>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>161</td>\n",
       "      <td>1339</td>\n",
       "      <td>3901</td>\n",
       "      <td>8980</td>\n",
       "      <td>11317</td>\n",
       "      <td>...</td>\n",
       "      <td>7894</td>\n",
       "      <td>11008</td>\n",
       "      <td>19574</td>\n",
       "      <td>11539</td>\n",
       "      <td>12734</td>\n",
       "      <td>7101</td>\n",
       "      <td>4336</td>\n",
       "      <td>3744</td>\n",
       "      <td>2101</td>\n",
       "      <td>784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100000007</td>\n",
       "      <td>84</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1102</td>\n",
       "      <td>5502</td>\n",
       "      <td>12087</td>\n",
       "      <td>35430</td>\n",
       "      <td>48382</td>\n",
       "      <td>...</td>\n",
       "      <td>26193</td>\n",
       "      <td>31473</td>\n",
       "      <td>42557</td>\n",
       "      <td>42853</td>\n",
       "      <td>58098</td>\n",
       "      <td>23348</td>\n",
       "      <td>15397</td>\n",
       "      <td>10904</td>\n",
       "      <td>6998</td>\n",
       "      <td>2172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100000008</td>\n",
       "      <td>64</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>394</td>\n",
       "      <td>484</td>\n",
       "      <td>1572</td>\n",
       "      <td>1737</td>\n",
       "      <td>...</td>\n",
       "      <td>3696</td>\n",
       "      <td>6451</td>\n",
       "      <td>10614</td>\n",
       "      <td>4042</td>\n",
       "      <td>4109</td>\n",
       "      <td>2867</td>\n",
       "      <td>2239</td>\n",
       "      <td>2569</td>\n",
       "      <td>1285</td>\n",
       "      <td>625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100000009</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>449</td>\n",
       "      <td>1576</td>\n",
       "      <td>4119</td>\n",
       "      <td>6485</td>\n",
       "      <td>...</td>\n",
       "      <td>9672</td>\n",
       "      <td>14838</td>\n",
       "      <td>21035</td>\n",
       "      <td>12352</td>\n",
       "      <td>10528</td>\n",
       "      <td>5048</td>\n",
       "      <td>2923</td>\n",
       "      <td>3560</td>\n",
       "      <td>2161</td>\n",
       "      <td>424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100000010</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>251</td>\n",
       "      <td>649</td>\n",
       "      <td>728</td>\n",
       "      <td>...</td>\n",
       "      <td>1431</td>\n",
       "      <td>3684</td>\n",
       "      <td>4943</td>\n",
       "      <td>1625</td>\n",
       "      <td>923</td>\n",
       "      <td>640</td>\n",
       "      <td>423</td>\n",
       "      <td>224</td>\n",
       "      <td>121</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Station_ID  H01  H02  H03  H04   H05   H06    H07    H08    H09  ...  \\\n",
       "0   100000006   39    0    0    0   161  1339   3901   8980  11317  ...   \n",
       "1   100000007   84    2    0    0  1102  5502  12087  35430  48382  ...   \n",
       "2   100000008   64    1    0    0     0   394    484   1572   1737  ...   \n",
       "3   100000009    0    0    0    0    40   449   1576   4119   6485  ...   \n",
       "4   100000010    0    0    0    0     4     8    251    649    728  ...   \n",
       "\n",
       "     H15    H16    H17    H18    H19    H20    H21    H22   H23   H24  \n",
       "0   7894  11008  19574  11539  12734   7101   4336   3744  2101   784  \n",
       "1  26193  31473  42557  42853  58098  23348  15397  10904  6998  2172  \n",
       "2   3696   6451  10614   4042   4109   2867   2239   2569  1285   625  \n",
       "3   9672  14838  21035  12352  10528   5048   2923   3560  2161   424  \n",
       "4   1431   3684   4943   1625    923    640    423    224   121    32  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_agg = df_q3.iloc[:, 1:].groupby(\"Station_ID\").sum().reset_index()\n",
    "df_q3_agg.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "59a806bd",
   "metadata": {
    "scrolled": true
   },
   "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>Station_ID</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100000006</td>\n",
       "      <td>H01</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100000007</td>\n",
       "      <td>H01</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100000008</td>\n",
       "      <td>H01</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100000009</td>\n",
       "      <td>H01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100000010</td>\n",
       "      <td>H01</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Station_ID variable  value\n",
       "0   100000006      H01     39\n",
       "1   100000007      H01     84\n",
       "2   100000008      H01     64\n",
       "3   100000009      H01      0\n",
       "4   100000010      H01      0"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_melt = df_q3_agg.melt(id_vars = \"Station_ID\")\n",
    "df_q3_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "8b4e9632",
   "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>Station_ID</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5588</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H01</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11177</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16766</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H03</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22355</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H04</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27944</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H05</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Station_ID variable  value\n",
       "5588    999033574      H01     12\n",
       "11177   999033574      H02      2\n",
       "16766   999033574      H03      0\n",
       "22355   999033574      H04      0\n",
       "27944   999033574      H05      0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_melt = df_q3_melt.sort_values([\"Station_ID\", \"variable\"], ascending = [False, True])\n",
    "df_q3_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "a65575b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def nor_minmax(x):\n",
    "    result = (x - x.min()) / (x.max() - x.min())\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "cf0f57d6",
   "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>Station_ID</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>value_nor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5588</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H01</td>\n",
       "      <td>12</td>\n",
       "      <td>0.444444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11177</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H02</td>\n",
       "      <td>2</td>\n",
       "      <td>0.074074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16766</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H03</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22355</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H04</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27944</th>\n",
       "      <td>999033574</td>\n",
       "      <td>H05</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Station_ID variable  value  value_nor\n",
       "5588    999033574      H01     12   0.444444\n",
       "11177   999033574      H02      2   0.074074\n",
       "16766   999033574      H03      0   0.000000\n",
       "22355   999033574      H04      0   0.000000\n",
       "27944   999033574      H05      0   0.000000"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_melt[\"value_nor\"] = df_q3_melt.groupby(\"Station_ID\")[\"value\"].transform(nor_minmax)\n",
    "df_q3_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "0cb93667",
   "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>variable</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>H05</th>\n",
       "      <th>H06</th>\n",
       "      <th>H07</th>\n",
       "      <th>H08</th>\n",
       "      <th>H09</th>\n",
       "      <th>H10</th>\n",
       "      <th>...</th>\n",
       "      <th>H15</th>\n",
       "      <th>H16</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Station_ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>100000006</th>\n",
       "      <td>0.001992</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.008225</td>\n",
       "      <td>0.068407</td>\n",
       "      <td>0.199295</td>\n",
       "      <td>0.458772</td>\n",
       "      <td>0.578165</td>\n",
       "      <td>0.504700</td>\n",
       "      <td>...</td>\n",
       "      <td>0.403290</td>\n",
       "      <td>0.562379</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.589506</td>\n",
       "      <td>0.650557</td>\n",
       "      <td>0.362777</td>\n",
       "      <td>0.221518</td>\n",
       "      <td>0.191274</td>\n",
       "      <td>0.107336</td>\n",
       "      <td>0.040053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000007</th>\n",
       "      <td>0.001446</td>\n",
       "      <td>0.000034</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.018968</td>\n",
       "      <td>0.094702</td>\n",
       "      <td>0.208045</td>\n",
       "      <td>0.609832</td>\n",
       "      <td>0.832765</td>\n",
       "      <td>0.799769</td>\n",
       "      <td>...</td>\n",
       "      <td>0.450842</td>\n",
       "      <td>0.541723</td>\n",
       "      <td>0.732504</td>\n",
       "      <td>0.737599</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.401873</td>\n",
       "      <td>0.265018</td>\n",
       "      <td>0.187683</td>\n",
       "      <td>0.120452</td>\n",
       "      <td>0.037385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000008</th>\n",
       "      <td>0.006030</td>\n",
       "      <td>0.000094</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.037121</td>\n",
       "      <td>0.045600</td>\n",
       "      <td>0.148106</td>\n",
       "      <td>0.163652</td>\n",
       "      <td>0.201055</td>\n",
       "      <td>...</td>\n",
       "      <td>0.348219</td>\n",
       "      <td>0.607782</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.380818</td>\n",
       "      <td>0.387130</td>\n",
       "      <td>0.270115</td>\n",
       "      <td>0.210948</td>\n",
       "      <td>0.242039</td>\n",
       "      <td>0.121067</td>\n",
       "      <td>0.058884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000009</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.001902</td>\n",
       "      <td>0.021345</td>\n",
       "      <td>0.074923</td>\n",
       "      <td>0.195816</td>\n",
       "      <td>0.308296</td>\n",
       "      <td>0.393202</td>\n",
       "      <td>...</td>\n",
       "      <td>0.459805</td>\n",
       "      <td>0.705396</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.587212</td>\n",
       "      <td>0.500499</td>\n",
       "      <td>0.239981</td>\n",
       "      <td>0.138959</td>\n",
       "      <td>0.169242</td>\n",
       "      <td>0.102734</td>\n",
       "      <td>0.020157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000010</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000809</td>\n",
       "      <td>0.001618</td>\n",
       "      <td>0.050779</td>\n",
       "      <td>0.131297</td>\n",
       "      <td>0.147279</td>\n",
       "      <td>0.115922</td>\n",
       "      <td>...</td>\n",
       "      <td>0.289500</td>\n",
       "      <td>0.745296</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.328748</td>\n",
       "      <td>0.186729</td>\n",
       "      <td>0.129476</td>\n",
       "      <td>0.085576</td>\n",
       "      <td>0.045317</td>\n",
       "      <td>0.024479</td>\n",
       "      <td>0.006474</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "variable         H01       H02  H03  H04       H05       H06       H07  \\\n",
       "Station_ID                                                               \n",
       "100000006   0.001992  0.000000  0.0  0.0  0.008225  0.068407  0.199295   \n",
       "100000007   0.001446  0.000034  0.0  0.0  0.018968  0.094702  0.208045   \n",
       "100000008   0.006030  0.000094  0.0  0.0  0.000000  0.037121  0.045600   \n",
       "100000009   0.000000  0.000000  0.0  0.0  0.001902  0.021345  0.074923   \n",
       "100000010   0.000000  0.000000  0.0  0.0  0.000809  0.001618  0.050779   \n",
       "\n",
       "variable         H08       H09       H10  ...       H15       H16       H17  \\\n",
       "Station_ID                                ...                                 \n",
       "100000006   0.458772  0.578165  0.504700  ...  0.403290  0.562379  1.000000   \n",
       "100000007   0.609832  0.832765  0.799769  ...  0.450842  0.541723  0.732504   \n",
       "100000008   0.148106  0.163652  0.201055  ...  0.348219  0.607782  1.000000   \n",
       "100000009   0.195816  0.308296  0.393202  ...  0.459805  0.705396  1.000000   \n",
       "100000010   0.131297  0.147279  0.115922  ...  0.289500  0.745296  1.000000   \n",
       "\n",
       "variable         H18       H19       H20       H21       H22       H23  \\\n",
       "Station_ID                                                               \n",
       "100000006   0.589506  0.650557  0.362777  0.221518  0.191274  0.107336   \n",
       "100000007   0.737599  1.000000  0.401873  0.265018  0.187683  0.120452   \n",
       "100000008   0.380818  0.387130  0.270115  0.210948  0.242039  0.121067   \n",
       "100000009   0.587212  0.500499  0.239981  0.138959  0.169242  0.102734   \n",
       "100000010   0.328748  0.186729  0.129476  0.085576  0.045317  0.024479   \n",
       "\n",
       "variable         H24  \n",
       "Station_ID            \n",
       "100000006   0.040053  \n",
       "100000007   0.037385  \n",
       "100000008   0.058884  \n",
       "100000009   0.020157  \n",
       "100000010   0.006474  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_pivot = pd.pivot(df_q3_melt, index = \"Station_ID\", columns = \"variable\", values = \"value_nor\")\n",
    "df_q3_pivot.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "e102c0d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "model = AgglomerativeClustering(n_clusters = 6).fit(df_q3_pivot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "67e36295",
   "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>variable</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>H05</th>\n",
       "      <th>H06</th>\n",
       "      <th>H07</th>\n",
       "      <th>H08</th>\n",
       "      <th>H09</th>\n",
       "      <th>H10</th>\n",
       "      <th>...</th>\n",
       "      <th>H16</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>cluster</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Station_ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>100000006</th>\n",
       "      <td>0.001992</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.008225</td>\n",
       "      <td>0.068407</td>\n",
       "      <td>0.199295</td>\n",
       "      <td>0.458772</td>\n",
       "      <td>0.578165</td>\n",
       "      <td>0.504700</td>\n",
       "      <td>...</td>\n",
       "      <td>0.562379</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.589506</td>\n",
       "      <td>0.650557</td>\n",
       "      <td>0.362777</td>\n",
       "      <td>0.221518</td>\n",
       "      <td>0.191274</td>\n",
       "      <td>0.107336</td>\n",
       "      <td>0.040053</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000007</th>\n",
       "      <td>0.001446</td>\n",
       "      <td>0.000034</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.018968</td>\n",
       "      <td>0.094702</td>\n",
       "      <td>0.208045</td>\n",
       "      <td>0.609832</td>\n",
       "      <td>0.832765</td>\n",
       "      <td>0.799769</td>\n",
       "      <td>...</td>\n",
       "      <td>0.541723</td>\n",
       "      <td>0.732504</td>\n",
       "      <td>0.737599</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.401873</td>\n",
       "      <td>0.265018</td>\n",
       "      <td>0.187683</td>\n",
       "      <td>0.120452</td>\n",
       "      <td>0.037385</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000008</th>\n",
       "      <td>0.006030</td>\n",
       "      <td>0.000094</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.037121</td>\n",
       "      <td>0.045600</td>\n",
       "      <td>0.148106</td>\n",
       "      <td>0.163652</td>\n",
       "      <td>0.201055</td>\n",
       "      <td>...</td>\n",
       "      <td>0.607782</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.380818</td>\n",
       "      <td>0.387130</td>\n",
       "      <td>0.270115</td>\n",
       "      <td>0.210948</td>\n",
       "      <td>0.242039</td>\n",
       "      <td>0.121067</td>\n",
       "      <td>0.058884</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000009</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.001902</td>\n",
       "      <td>0.021345</td>\n",
       "      <td>0.074923</td>\n",
       "      <td>0.195816</td>\n",
       "      <td>0.308296</td>\n",
       "      <td>0.393202</td>\n",
       "      <td>...</td>\n",
       "      <td>0.705396</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.587212</td>\n",
       "      <td>0.500499</td>\n",
       "      <td>0.239981</td>\n",
       "      <td>0.138959</td>\n",
       "      <td>0.169242</td>\n",
       "      <td>0.102734</td>\n",
       "      <td>0.020157</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100000010</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000809</td>\n",
       "      <td>0.001618</td>\n",
       "      <td>0.050779</td>\n",
       "      <td>0.131297</td>\n",
       "      <td>0.147279</td>\n",
       "      <td>0.115922</td>\n",
       "      <td>...</td>\n",
       "      <td>0.745296</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.328748</td>\n",
       "      <td>0.186729</td>\n",
       "      <td>0.129476</td>\n",
       "      <td>0.085576</td>\n",
       "      <td>0.045317</td>\n",
       "      <td>0.024479</td>\n",
       "      <td>0.006474</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "variable         H01       H02  H03  H04       H05       H06       H07  \\\n",
       "Station_ID                                                               \n",
       "100000006   0.001992  0.000000  0.0  0.0  0.008225  0.068407  0.199295   \n",
       "100000007   0.001446  0.000034  0.0  0.0  0.018968  0.094702  0.208045   \n",
       "100000008   0.006030  0.000094  0.0  0.0  0.000000  0.037121  0.045600   \n",
       "100000009   0.000000  0.000000  0.0  0.0  0.001902  0.021345  0.074923   \n",
       "100000010   0.000000  0.000000  0.0  0.0  0.000809  0.001618  0.050779   \n",
       "\n",
       "variable         H08       H09       H10  ...       H16       H17       H18  \\\n",
       "Station_ID                                ...                                 \n",
       "100000006   0.458772  0.578165  0.504700  ...  0.562379  1.000000  0.589506   \n",
       "100000007   0.609832  0.832765  0.799769  ...  0.541723  0.732504  0.737599   \n",
       "100000008   0.148106  0.163652  0.201055  ...  0.607782  1.000000  0.380818   \n",
       "100000009   0.195816  0.308296  0.393202  ...  0.705396  1.000000  0.587212   \n",
       "100000010   0.131297  0.147279  0.115922  ...  0.745296  1.000000  0.328748   \n",
       "\n",
       "variable         H19       H20       H21       H22       H23       H24  \\\n",
       "Station_ID                                                               \n",
       "100000006   0.650557  0.362777  0.221518  0.191274  0.107336  0.040053   \n",
       "100000007   1.000000  0.401873  0.265018  0.187683  0.120452  0.037385   \n",
       "100000008   0.387130  0.270115  0.210948  0.242039  0.121067  0.058884   \n",
       "100000009   0.500499  0.239981  0.138959  0.169242  0.102734  0.020157   \n",
       "100000010   0.186729  0.129476  0.085576  0.045317  0.024479  0.006474   \n",
       "\n",
       "variable    cluster  \n",
       "Station_ID           \n",
       "100000006         0  \n",
       "100000007         0  \n",
       "100000008         3  \n",
       "100000009         3  \n",
       "100000010         3  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_pivot[\"cluster\"] = model.labels_\n",
    "df_q3_pivot.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "ff57d416",
   "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>variable</th>\n",
       "      <th>Station_ID</th>\n",
       "      <th>H01</th>\n",
       "      <th>H02</th>\n",
       "      <th>H03</th>\n",
       "      <th>H04</th>\n",
       "      <th>H05</th>\n",
       "      <th>H06</th>\n",
       "      <th>H07</th>\n",
       "      <th>H08</th>\n",
       "      <th>H09</th>\n",
       "      <th>...</th>\n",
       "      <th>H16</th>\n",
       "      <th>H17</th>\n",
       "      <th>H18</th>\n",
       "      <th>H19</th>\n",
       "      <th>H20</th>\n",
       "      <th>H21</th>\n",
       "      <th>H22</th>\n",
       "      <th>H23</th>\n",
       "      <th>H24</th>\n",
       "      <th>cluster</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100000006</td>\n",
       "      <td>0.001992</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.008225</td>\n",
       "      <td>0.068407</td>\n",
       "      <td>0.199295</td>\n",
       "      <td>0.458772</td>\n",
       "      <td>0.578165</td>\n",
       "      <td>...</td>\n",
       "      <td>0.562379</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.589506</td>\n",
       "      <td>0.650557</td>\n",
       "      <td>0.362777</td>\n",
       "      <td>0.221518</td>\n",
       "      <td>0.191274</td>\n",
       "      <td>0.107336</td>\n",
       "      <td>0.040053</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100000007</td>\n",
       "      <td>0.001446</td>\n",
       "      <td>0.000034</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.018968</td>\n",
       "      <td>0.094702</td>\n",
       "      <td>0.208045</td>\n",
       "      <td>0.609832</td>\n",
       "      <td>0.832765</td>\n",
       "      <td>...</td>\n",
       "      <td>0.541723</td>\n",
       "      <td>0.732504</td>\n",
       "      <td>0.737599</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.401873</td>\n",
       "      <td>0.265018</td>\n",
       "      <td>0.187683</td>\n",
       "      <td>0.120452</td>\n",
       "      <td>0.037385</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100000008</td>\n",
       "      <td>0.006030</td>\n",
       "      <td>0.000094</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.037121</td>\n",
       "      <td>0.045600</td>\n",
       "      <td>0.148106</td>\n",
       "      <td>0.163652</td>\n",
       "      <td>...</td>\n",
       "      <td>0.607782</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.380818</td>\n",
       "      <td>0.387130</td>\n",
       "      <td>0.270115</td>\n",
       "      <td>0.210948</td>\n",
       "      <td>0.242039</td>\n",
       "      <td>0.121067</td>\n",
       "      <td>0.058884</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100000009</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.001902</td>\n",
       "      <td>0.021345</td>\n",
       "      <td>0.074923</td>\n",
       "      <td>0.195816</td>\n",
       "      <td>0.308296</td>\n",
       "      <td>...</td>\n",
       "      <td>0.705396</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.587212</td>\n",
       "      <td>0.500499</td>\n",
       "      <td>0.239981</td>\n",
       "      <td>0.138959</td>\n",
       "      <td>0.169242</td>\n",
       "      <td>0.102734</td>\n",
       "      <td>0.020157</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100000010</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000809</td>\n",
       "      <td>0.001618</td>\n",
       "      <td>0.050779</td>\n",
       "      <td>0.131297</td>\n",
       "      <td>0.147279</td>\n",
       "      <td>...</td>\n",
       "      <td>0.745296</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.328748</td>\n",
       "      <td>0.186729</td>\n",
       "      <td>0.129476</td>\n",
       "      <td>0.085576</td>\n",
       "      <td>0.045317</td>\n",
       "      <td>0.024479</td>\n",
       "      <td>0.006474</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "variable  Station_ID       H01       H02  H03  H04       H05       H06  \\\n",
       "0          100000006  0.001992  0.000000  0.0  0.0  0.008225  0.068407   \n",
       "1          100000007  0.001446  0.000034  0.0  0.0  0.018968  0.094702   \n",
       "2          100000008  0.006030  0.000094  0.0  0.0  0.000000  0.037121   \n",
       "3          100000009  0.000000  0.000000  0.0  0.0  0.001902  0.021345   \n",
       "4          100000010  0.000000  0.000000  0.0  0.0  0.000809  0.001618   \n",
       "\n",
       "variable       H07       H08       H09  ...       H16       H17       H18  \\\n",
       "0         0.199295  0.458772  0.578165  ...  0.562379  1.000000  0.589506   \n",
       "1         0.208045  0.609832  0.832765  ...  0.541723  0.732504  0.737599   \n",
       "2         0.045600  0.148106  0.163652  ...  0.607782  1.000000  0.380818   \n",
       "3         0.074923  0.195816  0.308296  ...  0.705396  1.000000  0.587212   \n",
       "4         0.050779  0.131297  0.147279  ...  0.745296  1.000000  0.328748   \n",
       "\n",
       "variable       H19       H20       H21       H22       H23       H24  cluster  \n",
       "0         0.650557  0.362777  0.221518  0.191274  0.107336  0.040053        0  \n",
       "1         1.000000  0.401873  0.265018  0.187683  0.120452  0.037385        0  \n",
       "2         0.387130  0.270115  0.210948  0.242039  0.121067  0.058884        3  \n",
       "3         0.500499  0.239981  0.138959  0.169242  0.102734  0.020157        3  \n",
       "4         0.186729  0.129476  0.085576  0.045317  0.024479  0.006474        3  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_pivot = df_q3_pivot.reset_index()\n",
    "df_q3_pivot.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "ab3bb02f",
   "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>Station_ID</th>\n",
       "      <th>cluster</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100000006</td>\n",
       "      <td>0</td>\n",
       "      <td>H01</td>\n",
       "      <td>0.001992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100000007</td>\n",
       "      <td>0</td>\n",
       "      <td>H01</td>\n",
       "      <td>0.001446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100000008</td>\n",
       "      <td>3</td>\n",
       "      <td>H01</td>\n",
       "      <td>0.006030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100000009</td>\n",
       "      <td>3</td>\n",
       "      <td>H01</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100000010</td>\n",
       "      <td>3</td>\n",
       "      <td>H01</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Station_ID  cluster variable     value\n",
       "0   100000006        0      H01  0.001992\n",
       "1   100000007        0      H01  0.001446\n",
       "2   100000008        3      H01  0.006030\n",
       "3   100000009        3      H01  0.000000\n",
       "4   100000010        3      H01  0.000000"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_pivot_melt = df_q3_pivot.melt(id_vars = [\"Station_ID\", \"cluster\"])\n",
    "df_q3_pivot_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "8d0b2ad6",
   "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>Station_ID</th>\n",
       "      <th>cluster</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "      <th>rush_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>34841</th>\n",
       "      <td>107000211</td>\n",
       "      <td>3</td>\n",
       "      <td>H07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35061</th>\n",
       "      <td>108000172</td>\n",
       "      <td>3</td>\n",
       "      <td>H07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35392</th>\n",
       "      <td>110000105</td>\n",
       "      <td>2</td>\n",
       "      <td>H07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35467</th>\n",
       "      <td>110000194</td>\n",
       "      <td>3</td>\n",
       "      <td>H07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36423</th>\n",
       "      <td>114000427</td>\n",
       "      <td>0</td>\n",
       "      <td>H07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Station_ID  cluster variable  value  rush_hour\n",
       "34841   107000211        3      H07    1.0          1\n",
       "35061   108000172        3      H07    1.0          1\n",
       "35392   110000105        2      H07    1.0          1\n",
       "35467   110000194        3      H07    1.0          1\n",
       "36423   114000427        0      H07    1.0          1"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_pivot_melt[\"rush_hour\"] = df_q3_pivot_melt[\"variable\"].isin([\"H07\", \"H08\", \"H09\"]) + 0\n",
    "df_q3_pivot_melt = df_q3_pivot_melt.loc[df_q3_pivot_melt[\"rush_hour\"] == 1, ]\n",
    "df_q3_pivot_melt = df_q3_pivot_melt.loc[df_q3_pivot_melt[\"value\"] == 1, ]\n",
    "df_q3_pivot_melt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "c8c9d72e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2761"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_q3_pivot_melt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "d473b3d4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2756"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_q3_pivot_melt.loc[:, [\"Station_ID\", \"cluster\"]].drop_duplicates())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "699ed9eb",
   "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>cluster</th>\n",
       "      <th>Station_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cluster  Station_ID\n",
       "0        0        1012\n",
       "1        1           2\n",
       "2        2         709\n",
       "3        3          19\n",
       "4        4         899\n",
       "5        5         115"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_q3_pivot_melt = df_q3_pivot_melt.loc[:, [\"Station_ID\", \"cluster\"]]\n",
    "df_q3_pivot_melt.groupby(\"cluster\")[\"Station_ID\"].nunique().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5a7d181",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}